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

    ISERROR ?? (Excel 2002)

    Hi

    I was hoping this would avoid #DIV/0! but it doesn't


    =IF((ISERROR(Q20-R20)/R20),0,(Q20-R20)/R20)

    Can anybody help please.

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

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

    Re: ISERROR ?? (Excel 2002)

    Misplaced parenthesis again:

    =IF(ISERROR((Q20-R20)/R20),0,(Q20-R20)/R20)

    Explanation: you want to see if the result of (Q20-R20)/R20 results in an error. So you look at ISERROR( (Q20-R20)/R20 )

    In your formula, ISERROR( Q20-R20 ) only looks at Q20-R20, but that will not result in an error unless Q20 or R20 contains an error value.

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

    Re: ISERROR ?? (Excel 2002)

    Hi Hans

    Thank you for your reply and explanation, I will edndeavor to learn from this.

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

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: ISERROR ?? (Excel 2002)

    This will always be simpler:

    =IF(R20,(Q20-R20)/R20,0)

    It uses the denominator's zero or non-zero status as a True/False argument to the IF().
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    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: ISERROR ?? (Excel 2002)

    But if either or both Q20 and/or R20 were an error of some kind, it would give an error message. This only eliminates the div/0 error caused by R20 being 0.

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: ISERROR ?? (Excel 2002)

    That's true, Steve, but as a matter of style I would either not tolerate an error in a source, or I would tolerate it in both places.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    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: ISERROR ?? (Excel 2002)

    I was just pointing out the difference. I prefer your formula, since it only eliminates the "expected error" from division by zero.

    As you imply, if something else caused the error, it would be better to know about it and not "ignore it"...

    Steve

Posting Permissions

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