| Home |
| Search |
| Today's Posts |
|
#1
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
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:D10)) ** 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
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:D10)) ** 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
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:D10)) 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:D10)) ** 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
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:D10)) 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:D10)) ** 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
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:D10)) 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:D10)) ** 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to combine information from multiple rows into one | Excel Discussion (Misc queries) | |||
| Pivot tables..how to change multiple information in rows ... | Excel Worksheet Functions | |||
| remove rows in one sheet from similar rows in another | Excel Worksheet Functions | |||
| Auto link rows of information from multiple sheets to single sheet | Excel Discussion (Misc queries) | |||
| How can I line up 2 columns with similar information? | Excel Discussion (Misc queries) | |||
Linear Mode
