Sales comparison percentage question

[Jezziebones]

I am monitoring a sales incentive program at work, and I have to create a
spreadsheet with quarterly sales from last year, quarterly sales from this
year, and a percent increase/decrease for each state. I am using this formula
for the % Change field with I2 being 1st Qtr 2008 totals, and E2 being 1st
Qtr 2007 totals

=(I2-E2)/ABS(E2)

which gives me the correct results, with the following exceptions. If all
the values are zeroes, I get the #DIV/0! result and I want it to display 0%;
if the sales from 2007 are a positive number, but the sales from 2008 were
zeroes, I get a -100% BUT if the sales from 2008 are a positive number, and
the sales from 2007 are zeroes, then the % Change field does not compute. Can
you tell me what I can do?

Thank you

 

[David Biddulph]

If you really want to show a change of zero when your 2007 figure was zero,
then use =IF(E2=0,0,(I2-E2)/ABS(E2)) , but note that then you won't see any
difference between:
a) the case when there is no change from 2007 to 2008, and
b) the case where 2007 had a figure of zero, but 2008 is non-zero.

It may be better to use something like
=IF(E2=0,"",(I2-E2)/ABS(E2)) or
=IF(E2=0,"starting from zero",(I2-E2)/ABS(E2)) or
=IF(E2=0,"new figure of "&I2&", starting from zero",(I2-E2)/ABS(E2))

 

[Jezziebones]

Thank you David. I'll give it a try.

If you really want to show a change of zero when your 2007 figure was zero,
then use =IF(E2=0,0,(I2-E2)/ABS(E2)) , but note that then you won't see any
difference between:
a) the case when there is no change from 2007 to 2008, and
b) the case where 2007 had a figure of zero, but 2008 is non-zero.

It may be better to use something like
=IF(E2=0,"",(I2-E2)/ABS(E2)) or
=IF(E2=0,"starting from zero",(I2-E2)/ABS(E2)) or
=IF(E2=0,"new figure of "&I2&", starting from zero",(I2-E2)/ABS(E2))