Iserror Function

[Ella Davis-Suggs]

ISERROR works where denominator is zero, but
not when both the numerator and demoniator are zero. Is
there a way around this?

 

[A.W.J. Ales]

Ellis,
Are you sure?
As far as I know (and have just tested on XL XP) 0/0 generates an error as
well.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Bernard Liengme]

Do you mean that you think 0/0 should NOT be an error? Do you feel it should
generate 1?
How about =IF(ABS(A1)+ABS(A2)=0,1,IF(ISERROR(A1/A2),"error",A1/A2))

 

[Ella Davis-SUggs]

Maybe this will help:

2004 2003 % Change
Goal is to get rid of the #DIV/0!: 100
0 #DIV/0!
0 0 #DIV/0!
50 100 -50%
100 50 100%
Maybe seeing how the worksheet is set up will help:

2004 2003 % Change
100 0 100%
0 0 100%
50 100 -50%
100 50 100%

The iserror function does correct the Div/0 error but the
% of change is incorrect when both the 2004 and 2003 data
is 0. It should state 0% because there was no change. Is
there a different way to write the formula?

 

[A.W.J. Ales]

Ella,

From you first posting I got the impression you mentioned that 0/0 wasn't
"catched" by ISERROR.
I answered that any value (including 0) divided by 0 would raise an error
which is noticed by ISERROR and thus tyou can "program around it"

To answer your question as it is now : You want 0% change if both original
value and new value are 0.
If however there is a new value and the old value was 0 there is - in my
opinion no "reasonable" %change calculation possible.
I give back then "Can't be calculated". You can replace that with whatever
you want it to be in that case.

=IF(A6=B6,0,IF(B6=0,"Can't be calculated",(A6-B6)/100)) ( Old value in
B6 ; New in A6 ; Formula and thus %CHANGE in C6)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[A.W.J. Ales]

Ella,
Sorry. Make that formula :
=IF(A6=B6,0,IF(B6=0,"Can't be calculated",(A6-B6)/B6))

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *