Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: MAX IF to ignore NA with multiple conditions

  1. #11
    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

    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.

  2. #12
    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
    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))
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #13
    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

    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!

  4. #14
    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
    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.
    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
  •