MAX IF to ignore NA with multiple conditions

[ellisbobby]

Hello all,

I'm trying to do something, which seems fairly simple in my head but I can't get to execute in a formula.

We record data daily on on a spreadsheet in a new column every day. Some days certain cells return a calculation, others return NA if there was no activity.

I originally changed the formula that would return NA to return a blank cell and that seemed to solve the issue. Then when I looked at the chart that read from the same data, it made all the blank cells read zero.

Is there a way to get the array below to read two conditions and ignore NA? I can get it to ignore NA with just one condition, but not two using =MAX(IF(NOT(ISNA.

Or I may be going about it all wrong. If I can get the chart to ignore the blank cells with a formula, that would work as well. Any help would be appreciated. Thanks in advance.

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

 

[ellisbobby]

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.

 

[Aladin Akyurek]

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))

 

[ellisbobby]

Aladin,

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

I appreciate your help. Thanks again!

 

[Aladin Akyurek]

Aladin,

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.