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

 

[mole999]

charts will accept #N/A as something not to display

 

[ellisbobby]

Hi mole999,

Thanks for the response. I can get the chart to accept #N/A, but I can't get the MAX(IF formula to ignore the #N/A when I have multiple conditions. Do you know if that's possible?

 

[mole999]

there is a function called AGGREGATE in 2013 onwards that may help, i'm not sure how to apply to your initial problem, it has to be possible i think

 

[ellisbobby]

I’m running 2010 at the moment. I tried AGGREGATE but couldn’t get it to do what I wanted it to do. I wonder if it will work on a later version of excel.

This seems fairly simple, just can’t figure it out.

 

[Aladin Akyurek]

In which range do you have #N/A?

 

[ellisbobby]

Hello Aladin,

Thanks for the reply. In the formula I listed above, the #N/A is in wow 24 of another sheet in the same workbook.

 

[Aladin Akyurek]

Hello Aladin,

Thanks for the reply. In the formula I listed above, the #N/A is in wow 24 of another sheet in the same workbook.

Control+shift+enter, not just enter:

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

 

[ellisbobby]

Yes, that’s how I apply the array and have the issue.

 

[Aladin Akyurek]

Yes, that’s how I apply the array and have the issue.

What is the issue you have? If #N/A is the value which occurs only in row 24 of data, then the formula should succeed.