|
| 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
June 14th, 2009, 12:48 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
June 14th, 2009, 02:04 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
max value from multiple rows of similar information
Try this array formula** :
=MAX(IF(A1:A10="white",D1  10))** 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
June 14th, 2009, 03:33 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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",D1 10))** 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
June 14th, 2009, 04:43 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
max value from multiple rows of similar information
Sure, just replace "white" with the cell reference:
F1 = white =MAX(IF(A1:A10=F1,D1 10))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",D1 10))** 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
June 14th, 2009, 05:30 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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,D1 10))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",D1 10))** 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
June 14th, 2009, 06:12 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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,D1 10))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",D1 10))** 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 | |
|
|
Linear Mode
