| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Finding Value Conditional
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 D1:D9 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?" |
|
#2
|
|||
|
|||
|
Sorry about the formatting mess in the original msg.
I have attached an HTML Doc. to look at an example of the sheet "John Galt" wrote in message ... 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 D1:D9 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?" |
|
#3
|
|||
|
|||
|
crl/shift/enter:
=MAX(IF(A1:A10=A21,D1:D10)) "John Galt" wrote in message ... 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 D1:D9 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?" |
|
#4
|
|||
|
|||
|
Well that didn't work
Let's try an attachment of a sample XLS. "John Galt" wrote in message ... Sorry about the formatting mess in the original msg. I have attached an HTML Doc. to look at an example of the sheet "John Galt" wrote in message ... 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 D1:D9 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?" |
|
#5
|
|||
|
|||
|
On Thu, 26 May 2005 18:53:47 GMT, "John Galt"
wrote: 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 D1:D9 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 |
|
#6
|
|||
|
|||
|
Thanks.
The 2nd formula worked out better for me. I made the necessary adjustments and voila! I appreciate your help! JG "Ron Rosenfeld" wrote in message ... On Thu, 26 May 2005 18:53:47 GMT, "John Galt" wrote: 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 D1:D9 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 |
|
#7
|
|||
|
|||
|
Thanks to you too!
I love multiple choices. I appreciate you taking the time to help. JG "Bob Umlas" wrote in message ... crl/shift/enter: =MAX(IF(A1:A10=A21,D1:D10)) "John Galt" wrote in message ... 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 D1:D9 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?" |
|
#8
|
|||
|
|||
|
On Thu, 26 May 2005 21:07:32 GMT, "John Galt"
wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting Error | Excel Worksheet Functions | |||
| How do i create a conditional sum? | Excel Worksheet Functions | |||
| conditional formatting conflict? | Excel Worksheet Functions | |||
| Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
| Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
