If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
max value from multiple rows of similar information
- A B C D
1 BROOK PINE 5/25 47 2 BROOK PINE 5/26 21 3 BROOK PINE 5/27 33 4 BROOK PINE 5/28 49 5 BROOK PINE 5/29 19 6 WHITE SNOW 5/25 23 7 WHITE SNOW 5/26 21 8 WHITE SNOW 5/27 77 9 WHITE SNOW 5/28 46 10 WHITE SNOW 5/29 22 From the above I want obtain the maximum value from column D but only from rows that match “WHITE” in column A which should be the 77 from cell D8. Thanks in advance |
#2
|
|||
|
|||
max value from multiple rows of similar information
Try this array formula** :
=MAX(IF(A1:A10="white",D110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Karesel" wrote in message ... - A B C D 1 BROOK PINE 5/25 47 2 BROOK PINE 5/26 21 3 BROOK PINE 5/27 33 4 BROOK PINE 5/28 49 5 BROOK PINE 5/29 19 6 WHITE SNOW 5/25 23 7 WHITE SNOW 5/26 21 8 WHITE SNOW 5/27 77 9 WHITE SNOW 5/28 46 10 WHITE SNOW 5/29 22 From the above I want obtain the maximum value from column D but only from rows that match "WHITE" in column A which should be the 77 from cell D8. Thanks in advance |
#3
|
|||
|
|||
max value from multiple rows of similar information
Is there a way to get the formula to pick up the cell with "WHITE" in it,
rather than entering "white" in the formula itself? Again, thanks in advance... "T. Valko" wrote: Try this array formula** : =MAX(IF(A1:A10="white",D110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Karesel" wrote in message ... - A B C D 1 BROOK PINE 5/25 47 2 BROOK PINE 5/26 21 3 BROOK PINE 5/27 33 4 BROOK PINE 5/28 49 5 BROOK PINE 5/29 19 6 WHITE SNOW 5/25 23 7 WHITE SNOW 5/26 21 8 WHITE SNOW 5/27 77 9 WHITE SNOW 5/28 46 10 WHITE SNOW 5/29 22 From the above I want obtain the maximum value from column D but only from rows that match "WHITE" in column A which should be the 77 from cell D8. Thanks in advance |
#4
|
|||
|
|||
max value from multiple rows of similar information
Sure, just replace "white" with the cell reference:
F1 = white =MAX(IF(A1:A10=F1,D110)) Don't forget, array entered! -- Biff Microsoft Excel MVP "Karesel" wrote in message ... Is there a way to get the formula to pick up the cell with "WHITE" in it, rather than entering "white" in the formula itself? Again, thanks in advance... "T. Valko" wrote: Try this array formula** : =MAX(IF(A1:A10="white",D110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Karesel" wrote in message ... - A B C D 1 BROOK PINE 5/25 47 2 BROOK PINE 5/26 21 3 BROOK PINE 5/27 33 4 BROOK PINE 5/28 49 5 BROOK PINE 5/29 19 6 WHITE SNOW 5/25 23 7 WHITE SNOW 5/26 21 8 WHITE SNOW 5/27 77 9 WHITE SNOW 5/28 46 10 WHITE SNOW 5/29 22 From the above I want obtain the maximum value from column D but only from rows that match "WHITE" in column A which should be the 77 from cell D8. Thanks in advance |
#5
|
|||
|
|||
max value from multiple rows of similar information
Thanks so much it's working...
"T. Valko" wrote: Sure, just replace "white" with the cell reference: F1 = white =MAX(IF(A1:A10=F1,D110)) Don't forget, array entered! -- Biff Microsoft Excel MVP "Karesel" wrote in message ... Is there a way to get the formula to pick up the cell with "WHITE" in it, rather than entering "white" in the formula itself? Again, thanks in advance... "T. Valko" wrote: Try this array formula** : =MAX(IF(A1:A10="white",D110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Karesel" wrote in message ... - A B C D 1 BROOK PINE 5/25 47 2 BROOK PINE 5/26 21 3 BROOK PINE 5/27 33 4 BROOK PINE 5/28 49 5 BROOK PINE 5/29 19 6 WHITE SNOW 5/25 23 7 WHITE SNOW 5/26 21 8 WHITE SNOW 5/27 77 9 WHITE SNOW 5/28 46 10 WHITE SNOW 5/29 22 From the above I want obtain the maximum value from column D but only from rows that match "WHITE" in column A which should be the 77 from cell D8. Thanks in advance |
#6
|
|||
|
|||
max value from multiple rows of similar information
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Karesel" wrote in message ... Thanks so much it's working... "T. Valko" wrote: Sure, just replace "white" with the cell reference: F1 = white =MAX(IF(A1:A10=F1,D110)) Don't forget, array entered! -- Biff Microsoft Excel MVP "Karesel" wrote in message ... Is there a way to get the formula to pick up the cell with "WHITE" in it, rather than entering "white" in the formula itself? Again, thanks in advance... "T. Valko" wrote: Try this array formula** : =MAX(IF(A1:A10="white",D110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Karesel" wrote in message ... - A B C D 1 BROOK PINE 5/25 47 2 BROOK PINE 5/26 21 3 BROOK PINE 5/27 33 4 BROOK PINE 5/28 49 5 BROOK PINE 5/29 19 6 WHITE SNOW 5/25 23 7 WHITE SNOW 5/26 21 8 WHITE SNOW 5/27 77 9 WHITE SNOW 5/28 46 10 WHITE SNOW 5/29 22 From the above I want obtain the maximum value from column D but only from rows that match "WHITE" in column A which should be the 77 from cell D8. Thanks in advance |
Thread Tools | |
Display Modes | |
|
|