# Thread: ISERROR and IF (EXCEL 2003)

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

3. ## Re: ISERROR and IF (EXCEL 2003)

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

4. ## 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. ## 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. ## Re: ISERROR and IF (EXCEL 2003)

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

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

9. ## Re: ISERROR and IF (EXCEL 2003)

Thanks, Hans!
Anything which avoids typpppping is worthwhile.

10. ## 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. ## Re: ISERROR and IF (EXCEL 2003)

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

is much shorter.

12. ## 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. ## 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,"---")

#### Posting Permissions

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