Results 1 to 13 of 13
Thread: ISERROR and IF (EXCEL 2003)

20040620, 01:26 #1
 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.

20040620, 03:50 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 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

20040620, 20:36 #3
 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.

20040620, 21:04 #4
 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?'

20040620, 21:20 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20040620, 22:00 #6
 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.

20040620, 22:18 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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",...)

20040620, 22:37 #8
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 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

20040620, 23:32 #9
 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.

20040622, 18:52 #10
 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

20040623, 12:06 #11
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 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

20040623, 15:53 #12
 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

20040623, 16:13 #13
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 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