ISERROR and IF (EXCEL 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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?’

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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”,…)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks, Hans!
Anything which avoids typpppping is worthwhile.

Reply | Quote

Thanks, Hans!
Anything which avoids typpppping is worthwhile.

Reply | Quote

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”,…)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?’

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

=IF(ISNUMBER(A1/B1),A1/B1,”—“)

is much shorter.

Reply | Quote

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

Reply | Quote

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,”—“)

Reply | Quote

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,”—“)

Reply | Quote

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

Reply | Quote

=IF(ISNUMBER(A1/B1),A1/B1,”—“)

is much shorter.

Reply | Quote

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

Reply | Quote