% Change Formula without #DIV/0!
 

(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 [b] is 2006
Column [C] is 2007
Column [D] would be the formula column showing % Change
if Column [C] is < Column [b] 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

% Change Formula without #DIV/0!
 

One way:

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

Format as a percentage.


In article ,
dj479794 wrote:

(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 [b] is 2006
Column [C] is 2007
Column [D] would be the formula column showing % Change
if Column [C] is < Column [b] 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

% Change Formula without #DIV/0!
 

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)
--
David Biddulph

"dj479794" wrote in message
...
(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 [b] is 2006
Column [C] is 2007
Column [D] would be the formula column showing % Change
if Column [C] is < Column [b] 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

% Change Formula without #DIV/0!
 

Percent change would be

(D - C)/C

Percent change is not valid when you start with a zero in column C.

"dj479794" wrote:

(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 [b] is 2006
Column [C] is 2007
Column [D] would be the formula column showing % Change
if Column [C] is < Column [b] 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

% Change Formula without #DIV/0!
 

=if(or(C1=0,C1=""),"",(c1-B1)/c1)
format as percent

"dj479794" wrote:

(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 [b] is 2006
Column [C] is 2007
Column [D] would be the formula column showing % Change
if Column [C] is < Column [b] 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

% Change Formula without #DIV/0!
 

Oops, fumble fingered again today:

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

In article ,
JE McGimpsey wrote:

One way:

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

Format as a percentage.


In article ,
dj479794 wrote:

(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 [b] is 2006
Column [C] is 2007
Column [D] would be the formula column showing % Change
if Column [C] is < Column [b] then % would be negative
if [C] is = [D] then no change or zero.

% Change Formula without #DIV/0!
 

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" wrote:

Oops, fumble fingered again today:

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

In article ,
JE McGimpsey wrote:

One way:

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

Format as a percentage.


In article ,
dj479794 wrote:

(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 [b] is 2006
Column [C] is 2007
Column [D] would be the formula column showing % Change
if Column [C] is < Column [b] then % would be negative
if [C] is = [D] then no change or zero.

% Change Formula without #DIV/0!
 

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...


In article ,
dj479794 wrote:

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.