Finding Value Conditional

[John Galt]

I am trying to find the largest value in column D for the year(Column A).

I want to build a formula in D21 that looks at the year in A21 and the
finds the highest value in column D19 for that Year

2003 would = 35,813.

Any help would be appreciated.
Thanks

A B C D E F G
ROW Year Date Note Odometer Miles Next Due Cost
1 2002 08/05/02 Oil Change 3,033 3,033
8,033 31.95
2 2002 10/04/02 Oil Change 8,028 4,995
13,028 31.95
3 2002 12/27/02 Oil Change 14,395 6,367
19,395 -
4 2003 04/02/03 Oil Change 20,691 6,296
28,191 68.26
5 2003 08/07/03 Oil Change 28,684 7,993
36,184 68.26
6 2003 12/13/03 Oil Change(DIY) 35,813 7,129
43,313 30.00
7 2004 10/14/04 Oil Change(DIY) Mobil 1 5 qts Valvoline
1.5 qts 43,697 7,884 50,197 30.00
8 2005 03/20/05 Oil Change(DIY) Mobil 1 6.5 qts
50,885 7,188 58,135 30.00
9 2005 05/26/05 Oil Change(Midas) Mobil 1 6.5 qts
56,687 5,802 63,937 52.99
10 2009 12/31/09 (56,687) 7,250





ROW Oil Change Other Maint
20 2002 Total Maintenance 63.90 -
63.90
21 2003 Total Maintenance ** Formula ** 166.52
44.64 211.16
22 2004 Total Maintenance 30.00 591.46
621.46
23 2005 Total Maintenance 82.99 145.14
228.13


John Galt
"Who is John Galt?"

 

[Bob Umlas]

crl/shift/enter:
=MAX(IF(A1:A10=A21,D110))

 

[Ron Rosenfeld]

I am trying to find the largest value in column D for the year(Column A).

I want to build a formula in D21 that looks at the year in A21 and the
finds the highest value in column D19 for that Year

2003 would = 35,813.

Any help would be appreciated.
Thanks
0 591.46
621.46
23 2005 Total Maintenance 82.99 145.14
228.13


John Galt
"Who is John Galt?"

=MAX((B3:B11=C16)*E3:E11)

entered with <ctrl><shift><enter> (array formula)

or:

=INDEX($E$3:$E$11,MATCH(C16,$B$3:$B$11))

You may need to adjust the references to match your Year and Odometer ranges.




--ron

 

[John Galt]

Thanks.
The 2nd formula worked out better for me.
I made the necessary adjustments and voila!
I appreciate your help!

JG

 

[John Galt]

Thanks to you too!
I love multiple choices.
I appreciate you taking the time to help.

JG

 

[Ron Rosenfeld]

Thanks.
The 2nd formula worked out better for me.
I made the necessary adjustments and voila!
I appreciate your help!

Glad to help.

Be sure your years are sorted in ascending order or the MATCH function may not
find the correct one. Also, any YEAR higher than the highest one in the table
will return the last value.

Thanks for the feedback.
--ron