Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: MAX IF to ignore NA with multiple conditions

  1. #1
    New Member
    Join Date
    Aug 2009
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MAX IF to ignore NA with multiple conditions

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

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,856
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: MAX IF to ignore NA with multiple conditions

    charts will accept #N/A as something not to display
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    New Member
    Join Date
    Aug 2009
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MAX IF to ignore NA with multiple conditions

    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?

  4. #4
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,856
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: MAX IF to ignore NA with multiple conditions

    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
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  5. #5
    New Member
    Join Date
    Aug 2009
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MAX IF to ignore NA with multiple conditions

    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.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: MAX IF to ignore NA with multiple conditions

    In which range do you have #N/A?
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Aug 2009
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MAX IF to ignore NA with multiple conditions

    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.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: MAX IF to ignore NA with multiple conditions

    Quote Originally Posted by ellisbobby View Post
    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))))
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    Aug 2009
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MAX IF to ignore NA with multiple conditions

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

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: MAX IF to ignore NA with multiple conditions

    Quote Originally Posted by ellisbobby View Post
    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.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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