Thread: MAX IF to ignore NA with multiple conditions Thanks:  1 Post #5259013 (1) Likes: 0

1. Re: MAX IF to ignore NA with multiple conditions

When I apply the array, the row I'm trying to find the maximum in (row 24 of another sheet) returns #N/A.

Simply put, I have a sheet called 'data' that has formulas in it. In row 24 of that sheet, I have formula =IF(E31>0,(number*number*E31),NA()) so that if there is no data in cell E31, it will return #N/A. I did it so that it automatically plots a chart on another sheet.

There is another sheet in the same file called 'trends' where I have two tables that show the average and max. In order for these tables to populate, they must meet two conditions.

{=MAX(IF('data'!\$3:\$3=\$A\$1,IF('data'!\$4:\$4=\$A15,'data'!\$24:\$24)))} - so if cell A1 of the 'trends' sheet shows up in row 3 of 'data' sheet, and if cell A15 of the 'trends' sheet shows up in row 4 of the 'data' sheet, it will find the maximum in row 24 of 'data' as long as the two conditions are met. If there is #N/A in any of those cells, it' returns #N/A in the table I created.  Reply With Quote

2. Re: MAX IF to ignore NA with multiple conditions Originally Posted by ellisbobby When I apply the array, the row I'm trying to find the maximum in (row 24 of another sheet) returns #N/A.
The row does not return #N/A, the formula does.

Simply put, I have a sheet called 'data' that has formulas in it. In row 24 of that sheet, I have formula =IF(E31>0,(number*number*E31),NA()) so that if there is no data in cell E31, it will return #N/A. I did it so that it automatically plots a chart on another sheet.
The formula as I edited

=MAX(IF(data!\$3:\$3=\$A\$1,IF(data!\$4:\$4=\$A16,IF(ISNUMBER(data!\$24:\$24),data!\$24:\$24))))

tests the values for being numbers and those which are numbers are delivered to the outer MAX, so if there are no #N/A's in other rows which are referred to
this formula should succeed.

There is another sheet in the same file called 'trends' where I have two tables that show the average and max. In order for these tables to populate, they must meet two conditions.

{=MAX(IF('data'!\$3:\$3=\$A\$1,IF('data'!\$4:\$4=\$A15,'data'!\$24:\$24)))} - so if cell A1 of the 'trends' sheet shows up in row 3 of 'data' sheet, and if cell A15 of the 'trends' sheet shows up in row 4 of the 'data' sheet, it will find the maximum in row 24 of 'data' as long as the two conditions are met. If there is #N/A in any of those cells, it' returns #N/A in the table I created.
It seems you have #N/A's in other rows too. In that case, let's try:

Control+shift+enter, not just enter...

=MAX(IF(ISNUMBER((data!\$3:\$3=\$A\$1)*(data!\$4:\$4=\$A16)*(data!\$24:\$24)),data!\$24:\$24))  Reply With Quote

3. Re: MAX IF to ignore NA with multiple conditions

Thank you so much! There were three of us trying to get this to work for week. Haha.

I appreciate your help. Thanks again!  Reply With Quote

4. Re: MAX IF to ignore NA with multiple conditions Originally Posted by ellisbobby Thank you so much! There were three of us trying to get this to work for week. Haha.

I appreciate your help. Thanks again!
You are welcome.  Reply With Quote

User Tag List

Tags for this Thread

blank, formula, ignore, read, return  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•