Change Percentage DIV/0! Error

[ajpowers]

I'm trying to see the change between (A) 2002 unit sales and (B)200
unit sales, and am using the following formula:

=(B-A)/A

This gives me the difference negative or positive, however if A (200
sales) is zero, then I get the DIV/0! error, how do I fix this?

Thank you

 

[mzehr]

Hi,
A couple of things you might want to consider. One is
that your formula will not take into account what the
change is if the prior month base amount is negative. To
fix that use the =IF(base=0,"",(current-base)/ABS(base))

This should help

 

[ajpowers]

None of the 2002 amounts are negative, it starts at zero

 

[Guest]

ajpowers,

You can also modify the existing formula to read as such:
=(B-A)/((A+.0000000001)

 

[Mike A]

I'm trying to see the change between (A) 2002 unit sales and (B)2003
unit sales, and am using the following formula:

=(B-A)/A

This gives me the difference negative or positive, however if A (2002
sales) is zero, then I get the DIV/0! error, how do I fix this?

Thank you.

Test for the error in your formula:

=if(iserr((B1-A1)/A1)),"",(B1-A1)/A1)



Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers

 

[Llobid]

Hi...

Your problem is not with your formula...it's with your math. You can'
find a percent increase or decrease of a zero value. Try using
modified formula.

=IF(A1=0,"N.A.",(B1-A1)/A1)

This will give you a proper percent increase or decrease in every cas
where it is mathematically possible

 

[JE McGimpsey]

Just bit more efficient:

=IF(A1,B1/A1-1,"N.A.")