range lookup

  • Thread starter Thread starter tamsky
  • Start date Start date
T

tamsky

Would anyone know how to create a formula that would locate the max $
amount for each year? See below



10/5/01 $10.03
8/14/00 $9.79
8/14/00 $9.50
5/22/00 $8.85
2/28/00 $8.36
12/20/99 $8.36
8/19/99 $8.05
8/17/98 $7.78
7/20/98 $7.92
3/9/98 $7.55
 
With the dates in A1:A10 formatted as text, one way is to array enter
(i.e., enter with Ctrl+Shift+Enter instead of just Enter)

=MAX((RIGHT(A$1:A$10,2)="00")*(B$1:B$10)) for the year 2000

Alan Beban
 
And if they're really dates (not text):

=MAX((YEAR(A$1:A$10)=2001)*(B$1:B$10))
also ctrl-shift-entered.

This may be easier to see what's happening:
=MAX(IF(YEAR(A$1:A$10)=2000,B$1:B$10))
(ctrl-shift-enter here, too.)
 
One more way if you have lots of years:

add a row of headers
select the range
data|pivottable
Follow the wizard until you get to a step with Layout on it.
click on that Layout button
drag the Date Header to the row field
drag the amount header to the data field
but double click on that "sum of amount"
change it to Max
finish up the wizard

Right click on one of the dates in the pivottable.
Select Group and show details
choose Year (and unselect any other options)
 
Back
Top