% Change Formula without #DIV/0!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

(Excel 2003)
Trying to find the % change (up or down) for the following data series. That
data includes zeros and effects my formula
Example:

Column is 2006
Column [C] is 2007
Column [D] would be the formula column showing % Change
if Column [C] is < Column then % would be negative
if [C] is = [D] then no change or zero.

CELL = [A1],[B1],[c1]

DATA:

SalespesonA,0,5
SaespersonB,4,6
SalespersonC,6,0
SalespersonD,8,3
 
In general your formula should be =(C1-B1)/B1, but of course the problem
arises when you are starting from zero as with salesperson A. You may wish
to use something like:
=IF(B1=0,"infinite increase",(C1-B1)/B1)
 
Percent change would be

(D - C)/C

Percent change is not valid when you start with a zero in column C.
 
Oops, fumble fingered again today:

D1: =IF(B1<>0, C1/B1-1, "N/A")

JE McGimpsey said:
One way:

D1: =IF(B1<>0,B1/C1-1,"N/A")

Format as a percentage.


dj479794 said:
(Excel 2003)
Trying to find the % change (up or down) for the following data series.
That
data includes zeros and effects my formula
Example:

Column is 2006
Column [C] is 2007
Column [D] would be the formula column showing % Change
if Column [C] is < Column then % would be negative
if [C] is = [D] then no change or zero.
Click to expand...
Click to expand...
 
Your formula works. Thanks. One concern. when there is data like:

2006 = 0
2007 = 4

It shows an increase of 100%. Should it not be 400% assuming all data must
be a whole number.

JE McGimpsey said:
Oops, fumble fingered again today:

D1: =IF(B1<>0, C1/B1-1, "N/A")

JE McGimpsey said:
One way:

D1: =IF(B1<>0,B1/C1-1,"N/A")

Format as a percentage.


dj479794 said:
(Excel 2003)
Trying to find the % change (up or down) for the following data series.
That
data includes zeros and effects my formula
Example:

Column is 2006
Column [C] is 2007
Column [D] would be the formula column showing % Change
if Column [C] is < Column then % would be negative
if [C] is = [D] then no change or zero.
Click to expand...
Click to expand...
Click to expand...
 
No, if you start with 0, the formula

=IF(B1<>0, C1/B1-1,"N/A")

will return "N/A", because the % increase is, by definition, undefined.

It's not 400%, because

2006 = 0.8
2007 = 4

is a 400% increase (e.g., 0.8 + 4*0.8).

Put another way, what number would you multiply by zero to get 4? That
(if it existed) would be the % increase...
 
Back
Top