(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

One way:

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

Format as a percentage.


In article <AC3C2014-17A8-4419-9365-(E-Mail Removed)>,
dj479794 <(E-Mail Removed)> 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

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" <(E-Mail Removed)> wrote in message
news:AC3C2014-17A8-4419-9365-(E-Mail Removed)...
> (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
>

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
>

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

Oops, fumble fingered again today:

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

In article <jemcgimpsey-(E-Mail Removed)>,
JE McGimpsey <(E-Mail Removed)> wrote:

> One way:
>
> D1: =IF(B1<>0,B1/C1-1,"N/A")
>
> Format as a percentage.
>
>
> In article <AC3C2014-17A8-4419-9365-(E-Mail Removed)>,
> dj479794 <(E-Mail Removed)> 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.
> >

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 <jemcgimpsey-(E-Mail Removed)>,
> JE McGimpsey <(E-Mail Removed)> wrote:
>
> > One way:
> >
> > D1: =IF(B1<>0,B1/C1-1,"N/A")
> >
> > Format as a percentage.
> >
> >
> > In article <AC3C2014-17A8-4419-9365-(E-Mail Removed)>,
> > dj479794 <(E-Mail Removed)> 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.
> > >
>

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 <EBC26B23-84F8-40E3-85D8-(E-Mail Removed)>,
dj479794 <(E-Mail Removed)> 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.