Creating a formula for sales comparison spreadsheet

  • Thread starter Thread starter Jezziebones
  • Start date Start date
J

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
 
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
Click to expand...

You have posted this message to the wrong newsgroup.
The access in this groups name refers to Microsoft Access, a database
program.
Please repost to the correct newsgroup for whatever Excel version you
are using. I would suggest you include your Windows and Office version
number in the message.
 
You have posted this message to the wrong newsgroup.
The access in this groups name refers to Microsoft Access, a database
program.
Please repost to the correct newsgroup for whatever Excel version you
are using. I would suggest you include your Windows and Office version
number in the message.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -
Click to expand...

=if(e2=0,0,(i2-e2)/abs(e2))
Chris M.
 
Back
Top