Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX only Rows in Range where another Row is Not Blank
I have some staggard data. There are categories in column D. There are
sub-categories in column E. Then some Descriptions in column F. And finally there are some corresponding numbers in column H. I'm trying to do a MAX function only on the values in column H where the cell in the same row in column F is NOT blank. So in my example, the max function should only be looking at rows (3,4,6,9,10,12,15,17,19,21,23). The MAX of the numbers in column H of these rows, as you know, would be 21. Is there a way to do this? Thanks. Paul D E F G H 1 Cat 24 2 SubCat 22 3 Desc1 20 4 Desc2 2 5 SubCat2 22 6 Desc1 2 7 Cat2 22 8 SubCat1 14 9 Desc1 8 10 Desc2 6 11 SubCat2 8 12 Desc1 8 13 Cat3 21 14 SubCat1 10 15 Desc1 21 16 SubCat2 6 17 Desc1 6 18 SubCat3 2 19 Desc1 2 20 SubCat4 2 21 Desc1 2 22 SubCat5 1 23 Desc1 1 -- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX only Rows in Range where another Row is Not Blank
Try this array formula** :
=MAX(IF(F1:F23<"",H1:H23)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PCLIVE" wrote in message ... I have some staggard data. There are categories in column D. There are sub-categories in column E. Then some Descriptions in column F. And finally there are some corresponding numbers in column H. I'm trying to do a MAX function only on the values in column H where the cell in the same row in column F is NOT blank. So in my example, the max function should only be looking at rows (3,4,6,9,10,12,15,17,19,21,23). The MAX of the numbers in column H of these rows, as you know, would be 21. Is there a way to do this? Thanks. Paul D E F G H 1 Cat 24 2 SubCat 22 3 Desc1 20 4 Desc2 2 5 SubCat2 22 6 Desc1 2 7 Cat2 22 8 SubCat1 14 9 Desc1 8 10 Desc2 6 11 SubCat2 8 12 Desc1 8 13 Cat3 21 14 SubCat1 10 15 Desc1 21 16 SubCat2 6 17 Desc1 6 18 SubCat3 2 19 Desc1 2 20 SubCat4 2 21 Desc1 2 22 SubCat5 1 23 Desc1 1 -- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX only Rows in Range where another Row is Not Blank
That's it. Thanks.
-- "T. Valko" wrote in message ... Try this array formula** : =MAX(IF(F1:F23<"",H1:H23)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PCLIVE" wrote in message ... I have some staggard data. There are categories in column D. There are sub-categories in column E. Then some Descriptions in column F. And finally there are some corresponding numbers in column H. I'm trying to do a MAX function only on the values in column H where the cell in the same row in column F is NOT blank. So in my example, the max function should only be looking at rows (3,4,6,9,10,12,15,17,19,21,23). The MAX of the numbers in column H of these rows, as you know, would be 21. Is there a way to do this? Thanks. Paul D E F G H 1 Cat 24 2 SubCat 22 3 Desc1 20 4 Desc2 2 5 SubCat2 22 6 Desc1 2 7 Cat2 22 8 SubCat1 14 9 Desc1 8 10 Desc2 6 11 SubCat2 8 12 Desc1 8 13 Cat3 21 14 SubCat1 10 15 Desc1 21 16 SubCat2 6 17 Desc1 6 18 SubCat3 2 19 Desc1 2 20 SubCat4 2 21 Desc1 2 22 SubCat5 1 23 Desc1 1 -- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX only Rows in Range where another Row is Not Blank
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "PCLIVE" wrote in message ... That's it. Thanks. -- "T. Valko" wrote in message ... Try this array formula** : =MAX(IF(F1:F23<"",H1:H23)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "PCLIVE" wrote in message ... I have some staggard data. There are categories in column D. There are sub-categories in column E. Then some Descriptions in column F. And finally there are some corresponding numbers in column H. I'm trying to do a MAX function only on the values in column H where the cell in the same row in column F is NOT blank. So in my example, the max function should only be looking at rows (3,4,6,9,10,12,15,17,19,21,23). The MAX of the numbers in column H of these rows, as you know, would be 21. Is there a way to do this? Thanks. Paul D E F G H 1 Cat 24 2 SubCat 22 3 Desc1 20 4 Desc2 2 5 SubCat2 22 6 Desc1 2 7 Cat2 22 8 SubCat1 14 9 Desc1 8 10 Desc2 6 11 SubCat2 8 12 Desc1 8 13 Cat3 21 14 SubCat1 10 15 Desc1 21 16 SubCat2 6 17 Desc1 6 18 SubCat3 2 19 Desc1 2 20 SubCat4 2 21 Desc1 2 22 SubCat5 1 23 Desc1 1 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming a non-blank range from a col containing non-consec. blank c | Excel Worksheet Functions | |||
How to fill in range of blank rows wth data fr last row previos ra | Excel Discussion (Misc queries) | |||
MACRO HELP - deleting rows containing a range of blank cells | Excel Discussion (Misc queries) | |||
Sumif to return a blank if sum range is blank | Excel Worksheet Functions | |||
Hide columns & rows that contain "0" or blank in a range of cells | Excel Worksheet Functions |