Recognizing Unstable Values in Excel

[Ken]

I apologize if someone already saw or answered this question ... I can't find
which subcategory of Excel groups where it apparently was posted earlier.
My question is how can you write an if then statement that recognizes that
the cell has an unstable answer. When I use the statement,
IF(A1="#DIV/0!",1), the cell doesn't return the value of "1" when the values
are unstable. How would I do this?

 

[Mike H]

Try,

=ISERROR(A1)*1

Mike

 

[Gary''s Student]

=IF(ISNUMBER(A1),"Stable","Unstable")

So if A1 contains a formula that returns #DIV/0!
or any other error, the formula above will display Unstable

 

[Teethless mama]

=--(ERROR.TYPE(A1)=2)

 

[Ron Rosenfeld]

I apologize if someone already saw or answered this question ... I can't find
which subcategory of Excel groups where it apparently was posted earlier.
My question is how can you write an if then statement that recognizes that
the cell has an unstable answer. When I use the statement,
IF(A1="#DIV/0!",1), the cell doesn't return the value of "1" when the values
are unstable. How would I do this?

I don't know what you mean by "unstable", but one way to test specifically for
a DIV/0 error in A1 is:

=if(ERROR.TYPE(A1)=2,1)


--ron

 

[Dave Peterson]

You may want to check for an error first--to avoid having an error returned:

=IF(ISERROR(A1),IF(ERROR.TYPE(A1)=2,1,"not Div/0"),"not error")

 

[ShaneDevenshire]

Hi,

Excel contains 7 built-in error types of which DIV/0 is one, the others are
Error Type#
#NULL! 1
#DIV/0! 2
#VALUE! 3
#REF! 4
#NAME? 5
#NUM! 6
#N/A 7

You can trap these with the following functions:
=ISERR(A1)
=ISNA(A1)
=ISERROR(A1)
=ERROR.TYPE(A1)

You usually combine on of the above with an IF statement such as
=IF(ISERR(A1),"",A1)
However, it is common when you get a DIV/0 message to consider what is
causing that and trapping that rather than the error itself, for example
If A1=0 the formula =B1/A1 would return the DIV/0 error, so we would trap
the value of A1 as follows:
=IF(A1=0,"",B1/A1)
In this case if A1 is blank or 0, the formula will show nothing in the cells.