Iserror not working as expected

[NathanG]

Hi,

I have a requirement to surpress all iserros i.e. #div/0!
Say this iserror is in cell A1. In A2 I have written thid

=IF(ISERROR(A1),0,A1)

That to me means if A1 contain an iserror make it 0 but if its anything else
leave what is there. Cell A1 will have a calculation formula in.

Why isn't that working? All that happens is that cell A2 displays a 0. Is it
actually saying make cell a2 a 0 if there is an iserror in cell A1??


Thanks

Nathan

 

[John Bundy]

If that is the result you are getting, is A1 actually producing an error? try
typing just a single number in there, does it return the number in A2 or 0?

 

[Ron Rosenfeld]

Hi,

I have a requirement to surpress all iserros i.e. #div/0!
Say this iserror is in cell A1. In A2 I have written thid

=IF(ISERROR(A1),0,A1)

That to me means if A1 contain an iserror make it 0 but if its anything else
leave what is there. Cell A1 will have a calculation formula in.

Why isn't that working? All that happens is that cell A2 displays a 0. Is it
actually saying make cell a2 a 0 if there is an iserror in cell A1??

The formula says:

If the results of the function in A1 is an ERROR, then display a 0.

Since the formula is in A2, that is where the result of the formula in A2 will
be displayed.

A FUNCTION returns a result.

If you want A1 to display a 0 if there is an error result, then, in CELL A1,
place of formula of the type:

A1: =IF(ISERROR(your_original_A1_formula), 0, your_original_A1_formula)


--ron

 

[NathanG]

I enter 2 instead of the formula in A1 and A2 became 2. A1 definitely is an
ISERROR as its #DIV/0! and I have checked the excel help and it listed the
errors.

????

Nathan

 

[NathanG]

Ron,

Your a legend mate!

Nathan

The formula says:

If the results of the function in A1 is an ERROR, then display a 0.

Since the formula is in A2, that is where the result of the formula in A2 will
be displayed.

A FUNCTION returns a result.

If you want A1 to display a 0 if there is an error result, then, in CELL A1,
place of formula of the type:

A1: =IF(ISERROR(your_original_A1_formula), 0, your_original_A1_formula)


--ron

 

[Ron Rosenfeld]

Ron,

Your a legend mate!

Nathan

Glad to help.

It is a common misunderstanding.
--ron