Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ISERROR and IF (EXCEL 2003)

    I have a cell that can either contain an error message, the result from a formula, or a "---" if the contents of a cell is blank.

    What I would like to do is supress any error message and show "---" in the case of error or a select cell is blank and
    otherwise show the results of formula. I can handle the IF condition, but combining the ISERROR and IF is the problem.

  2. #2
    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 and IF (EXCEL 2003)

    The general way to do this is

    =IF(OR(ISTEXT(cell),ISERROR(formula)),"---",formula)

    where formula is the exact same reference or formula in both parts of the expression. However if you could post your actual expression there may be a more concise way to handle the blank and the error.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISERROR and IF (EXCEL 2003)

    This does it (I think) with a nested IF statement.
    See attached.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISERROR and IF (EXCEL 2003)

    As an interesting (?) supplementary to this, on a large workbook with loads of IFs, is there any mileage in setting up nested IFs so that the most likely (most frequent) result is reached as soon as possible? Or, being dumb, does excel plough on testing the subsequent IFs even though it's already got the 'right' answer?
    The supplementary to the supplementary is, 'How do you know?'

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

    Re: ISERROR and IF (EXCEL 2003)

    As far as I know, Excel stops evaluating a formula as soon at it obtains a value. As an experiment, enter a number in cell A1, and enter this formula in cell B1:

    =IF(A1>0,"Greater than zero",IF(A1>10,"Greater than ten"))

    If the value in cell A1 is 20, for example, the result of the formula is "Greater than zero". Although A1>10 is also true, this part is not evaluated.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISERROR and IF (EXCEL 2003)

    Nice test, Hans. Attched is a modest extension, suggesting you're right.

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

    Re: ISERROR and IF (EXCEL 2003)

    Note: in your formula, you don't really need to add =TRUE after ISTEXT(A1). The function ISTEXT(A1) already results in either TRUE or FALSE, so it can be used as a condition directly, without comparing it to TRUE:

    =IF(ISTEXT(A1),"text",...)

  8. #8
    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 and IF (EXCEL 2003)

    Which brings me back to the OP; ISNUMBER() handles error returns, so that if a formula errors out or refers to text, a more concise answer to the OP could be in the form

    =IF(ISNUMBER(formula),formula,"---")

    but we'll have to wait until we get clarification from the OP. Meanwhile, I'm going to have a beer.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISERROR and IF (EXCEL 2003)

    Thanks, Hans!
    Anything which avoids typpppping is worthwhile.

  10. #10
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISERROR and IF (EXCEL 2003)

    I think something like this would work for you. It places --- for errors and also when either or both cells involved in the needed formula are empty and runs the formula when both cells have numbers.

    yoyoPHIL

  11. #11
    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 and IF (EXCEL 2003)

    =IF(ISNUMBER(A1/B1),A1/B1,"---")

    is much shorter.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISERROR and IF (EXCEL 2003)

    There is a problem with your formula when there is no number in the A cell but the B cell has a number. I believe that we want to show --- in this instance. Your formula will calculate to 0 because an empty cell divided by a number is 0 according to excel and the 0 is a number.

    See attached

    yoyoPHIL

  13. #13
    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 and IF (EXCEL 2003)

    I wouldn't call that issue a problem, and we'll never know if the OP was referring to one cell or two until he or she posts back. And there's always:

    =IF(ISNUMBER(A1)*ISNUMBER(B1),A1/B1,"---")
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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