Array Formula not working
Results 1 to 5 of 5

Thread: Array Formula not working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2006
    Location
    Australia
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Array Formula not working

    Hello...

    I'm trying to do a 'MAX IF' array... and I THINK I have the formula right, but it's returning a #N/A

    This is my forumla - and I am remembering to CSE to exit it, etc.

    =MAX(IF($A$3:$A$65536=A4,$Q$3:Q$65536,FALSE))

    It should definately have an answer for the line I'm testing it on. I thought it was because the values in Q are dates, but I changed them to be straight values and it still returned a #N/A.

    Have I missed something??

  2. #2
    MrExcel MVP Andrew Fergus's Avatar
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5,425
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Array Formula not working

    Hi

    Two thoughts. If you know the minimum value will be greater than zero (which a date is) then change the FALSE part to 0. {ie zero}

    Secondly (at the risk of stating the obvious - forgive me if you have already checked this) check that both columns A and Q do not contain any errors - in particular a "N/A" error.

    Andrew
    ~ >*()))><(


    I'd rather have a full bottle in front of me than a full frontal lobotomy.

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

    Default Re: Array Formula not working

    Do you have #N/A in the ranges the formulas refer to?
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    Board Regular
    Join Date
    Aug 2006
    Location
    Australia
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array Formula not working

    Thanks guys!

    Sometimes it's useful to have an extra brain or two.

    I did, in fact, have a #N/A in my array. I thought I had checked for them before, so I discounted that as a reason - but there it is!


    Thanks!!

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

    Default Re: Array Formula not working

    Quote Originally Posted by cmhoz View Post
    Thanks guys!

    Sometimes it's useful to have an extra brain or two.

    I did, in fact, have a #N/A in my array. I thought I had checked for them before, so I discounted that as a reason - but there it is!


    Thanks!!
    Both ranges?

    You can take that into account in the formula...
    Code:
    =MAX(
       IF(ISNA($A$3:$A$65536),
       IF($A$3:$A$65536=A4,
         $Q$3:Q$65536)))
    Code:
    =MAX(
       IF($A$3:$A$65536=A4,
       IF(ISNUMBER($Q$3:Q$65536),
         $Q$3:Q$65536)))
    Code:
    =MAX(
       IF((1-ISNA($A$3:$A$65536))*ISNUMBER($Q$3:Q$65536),
       IF($A$3:$A$65536=A4,
         $Q$3:Q$65536)))
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

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
  •