range lookup

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
 
A

Alan Beban

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
 
D

Dave Peterson

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.)
 
D

Dave Peterson

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)
 
Top