#DIV/0 problem

[Shakin97]

I have a spreadsheet that is supposed to calculate % change usin
positive or negative numbers. It seems to work fine until of course
try to divide by 0. I use this for watching trends in earnings pe
share(EPS) for companies I invest in. For example:
Lucent has a current EPS of 0.00(A1) for the end of the year. Nex
week comes along and there are no revisions up or down and reamains a
0(B1). That would be B1-A1/A1 = #DIV/0
I need this expanded IF statement to cover if the denominator reache
the value of 0.00 in future.

This is the current IF statement that I'm using:

=IF(A1>0,(B1-A1)/A1,-(B1-A1)/A1)

Troubled...

 

[Dan E]

=IF(A1<>0,IF(A1>0,(B1-A1)/A1,-(B1-A1)/A1),"inf")

Dan E

 

[J.E. McGimpsey]

one way:

=IF(A1=0,"",SIGN(A1)*(B1/A1-1))

 

[BrianB]

Try this :-

=IF(A1=0,0,IF(A1>0,(B1-A1)/A1,-(B1-A1)/A1)