Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Can I use ISNA here (Excel 2002)

    Hi

    This fomula =IF(AC7=0,"",AC7/Y7) returns #NA, is it possible to use ISNA to return a blank cell the same as you can in VLOOKUP?


    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Can I use ISNA here (Excel 2002)

    Something like this:
    =IF(isna(ac7/y7),"",if(AC7=0),"",AC7/Y7))

    This will not yield a blank cell, it will yield a cell containing a null string. It is not possible to have a blank (ie empty) cell if you put a formula into it.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Can I use ISNA here (Excel 2002)

    Hi Steve

    Thanks for the reply a null value is fine, however using your formula give me an error where the first "" are highlighted.

    Thanks Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can I use ISNA here (Excel 2002)

    There is one closing parenthesis too many. Try

    =IF(ISNA(AC7/Y7),"",IF(AC7=0,"",AC7/Y7))

    By the way, shouldn't you test for Y7=0 instead of fot AC7=0?

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Can I use ISNA here (Excel 2002)

    Hi Hans

    Your formula acheived what I am looking for.

    incedently Y7 contains another formula, but I wil do some checking.

    Thanks for your reply

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Can I use ISNA here (Excel 2002)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> OOPS. Hans found/fixed my error. That is what I get for posting an "air-formula"...

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can I use ISNA here (Excel 2002)

    Apparently, either AC7 or Y7 can house #N/A for the formula to evaluate to #N/A.

    If you don't want to clean up AC7 and/or Y7 for such an error, as an alternative, you can set up your formula in a positive mood...

    =IF(ISNUMBER(AC7/Y7),AC7/Y7,"")
    Microsoft MVP - Excel

Posting Permissions

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