Formula Error or Data Error?

I have the following spreadsheet which I attempting to calculate the Prior
Years Inventory Level in actual Retail $
I have a database query that retrieves the following Information:(
DeptDate( week end date(Saturday), OH Retail$, % Chg Prior Year), DeptNum
DeptName, CatName

(Dept = Abbreviation for Department)
DeptDate = (the date(Saturday) week ending (i.e. 5/10/08_
OH Retail$ = The Retail $ of Inventory for that department
OH %Chg = % chg from Prior Year (Don't have access to Original Data for a
certain Period of Time)
Deptnum = Department #
DeptName = Department Desc
CatName = Category Name (where different sets of deptnums are grouped)

The problem I am having is it doesn't seem to cross check the math.
I have a pivot table to display the OH$ , I then have 2 cols on the same
work sheet that calculates the prior years OH$
but when I cross check it doesn't seem to be right.

Col B (Calculated) has the following formula =G3/(1-(C3)*-1)
Col C = Contains the OH %CHG for each Dept/Cat for that week
COL D (Begins Pivot Table) = Contain Category Name
COL E = DeptNum
COL F = DeptName
COL G (Being Data) = Date on Column headers, and value of Inventory Level
The Problem occurs which I cross check the math.
COL A Contains =SUM(B3:B13)
If I summarize(COL A) the value in the range for each category (All Deptnum
in Category A for example it doesn't match)
am I doing something wrong Mathematically or is the data possibly bad? See
below for data sample
The issue is $104,007 with a (21.9)% chg = $133,182 not $126,785 the sum of
the individual changes
I have confirmed thet query data matches the database data

Thanks for any Help



A B C D
E F G
LY OnHand Retail$ DeptDate
5/3/08 Diff Category Dept# DeptDesc 5/3/2008
(row 3) $50,071 -40.90% Consumables 110 Food $29,592
$909 71.90% 111 DSD $1,563
$4,509 -45.40% 114 Speciality Food $2,462
$0 890.00% 115 Frozen Food
$43,248 -18.90% 120 HBC $35,074
$0 121 Control Sub
$5,110 -8.30% 130 House Keeping $4,686
$7,561 -14.90% 140 Household Chemicals $6,434
$4,811 -14.30% 150 Paper $4,123
$10,081 31.60% 160 Pet $13,266
$6,883 -1.10% 170 Home Organization $6,807
$133,182 $126,785 -21.90% Consumables Total $104,007

Kevin
Wed May 07 09:17:43 CDT 2008

sorry data didnt format well , this is a little better


> A B C D E F
> G
> OnHand Diff Dept# DeptDesc
> 5/3/2008
> $50,071 -40.90% 110 Food
> $29,592
> $909 71.90% 111 DSD
> $1,563
> $4,509 -45.40% 114 Speciality Food
> $2,462
> $0 890.00% 115 Frozen Food
> $43,248 -18.90% 120 HBC
> $35,074
> $0 121 Control Sub
> $5,110 -8.30% 130 House Keeping
> $4,686
> $7,561 -14.90% 140 Household Chemicals
> $6,434
> $4,811 -14.30% 150 Paper
> $4,123
> $10,081 31.60% 160 Pet
> $13,266
> $6,883 -1.10% 170 Home Organization
> $6,807
$126,785 -21.90% Consumables Total
$104,007


"Kevin Labore" <kevinl@somewhere.net> wrote in message
news:ea-dnY3XJLJOKrzVnZ2dnUVZ_iydnZ2d@comcast.com...
>I have the following spreadsheet which I attempting to calculate the Prior
>Years Inventory Level in actual Retail $
> I have a database query that retrieves the following Information:(
> DeptDate( week end date(Saturday), OH Retail$, % Chg Prior Year), DeptNum
> DeptName, CatName
>
> (Dept = Abbreviation for Department)
> DeptDate = (the date(Saturday) week ending (i.e. 5/10/08_
> OH Retail$ = The Retail $ of Inventory for that department
> OH %Chg = % chg from Prior Year (Don't have access to Original Data for
> a certain Period of Time)
> Deptnum = Department #
> DeptName = Department Desc
> CatName = Category Name (where different sets of deptnums are grouped)
>
> The problem I am having is it doesn't seem to cross check the math.
> I have a pivot table to display the OH$ , I then have 2 cols on the same
> work sheet that calculates the prior years OH$
> but when I cross check it doesn't seem to be right.
>
> Col B (Calculated) has the following formula =G3/(1-(C3)*-1)
> Col C = Contains the OH %CHG for each Dept/Cat for that week
> COL D (Begins Pivot Table) = Contain Category Name
> COL E = DeptNum
> COL F = DeptName
> COL G (Being Data) = Date on Column headers, and value of Inventory Level
> The Problem occurs which I cross check the math.
> COL A Contains =SUM(B3:B13)
> If I summarize(COL A) the value in the range for each category (All
> Deptnum in Category A for example it doesn't match)
> am I doing something wrong Mathematically or is the data possibly bad?
> See below for data sample
> The issue is $104,007 with a (21.9)% chg = $133,182 not $126,785 the sum
> of the individual changes
> I have confirmed thet query data matches the database data
>
> Thanks for any Help
>
>
>
> A B C D E
> F G
> LY OnHand Retail$ DeptDate
> 5/3/08 Diff Category Dept# DeptDesc 5/3/2008
> (row 3) $50,071 -40.90% Consumables 110 Food $29,592
> $909 71.90% 111 DSD $1,563
> $4,509 -45.40% 114 Speciality Food $2,462
> $0 890.00% 115 Frozen Food
> $43,248 -18.90% 120 HBC $35,074
> $0 121 Control Sub
> $5,110 -8.30% 130 House Keeping $4,686
> $7,561 -14.90% 140 Household Chemicals $6,434
> $4,811 -14.30% 150 Paper $4,123
> $10,081 31.60% 160 Pet $13,266
> $6,883 -1.10% 170 Home Organization $6,807
> $133,182 $126,785 -21.90% Consumables Total $104,007
>
>
>
>
>
>

joeu2004
Thu May 08 00:11:18 CDT 2008

On May 7, 6:08 am, "Kevin Labore" <kev...@somewhere.net> wrote:
> The issue is $104,007 with a (21.9)% chg =3D $133,182
> not $126,785 the sum of the individual changes

I don't see how you compute $126,785 as "the sum of
individual changes". But I do note that $126,792 is
about 104007*(1-(-21.91%)), where the total declined
by 21.91% from $133,182 to $104,007.

I assume that is the gist of your mistake.

For example, if Food declined by 40.90% from $50,071
to $29,592, you can compute $29,592 by 50071*(1-40.90%).
You can compute $50,071 by 29592/(1-40.90%), not
29592*(1-(-40.90%)).

I suspect you used the sum of the latter to compute
"the sum of the individual changes".

But you wrote:
> Col B (Calculated) has the following formula
> =3DG3/(1-(C3)*-1)

That's a perverse way of writing simply =3DG3/(1+C3). That seems to be
correct, if G3 is $29,592 and C3 is -40.90%, using my example above.


----- original posting -----

On May 7, 6:08=A0am, "Kevin Labore" <kev...@somewhere.net> wrote:
> I have the following spreadsheet which I attempting to calculate the Prior=

> Years Inventory Level in actual Retail $
> I have a database query that retrieves the following Information:(
> DeptDate( week end date(Saturday), OH Retail$, % Chg Prior Year), DeptNum
> DeptName, CatName
>
> (Dept =3D Abbreviation for Department)
> DeptDate =A0 =A0 =3D =A0 =A0(the date(Saturday) week ending (i.e. 5/10/08_=

> OH Retail$ =A0=3D =A0 =A0The Retail $ of Inventory for that department
> OH %Chg =A0 =A0=3D % chg from Prior Year (Don't have access to Original Da=
ta for a
> certain Period of Time)
> Deptnum =A0 =A0 =3D =A0 =A0Department #
> DeptName =A0 =A0=3D =A0 =A0Department Desc
> CatName =A0 =A0=3D =A0Category Name (where different sets of deptnums are =
grouped)
>
> The problem I am having is it doesn't seem to cross check the math.
> I have a pivot table to display the OH$ , I then have 2 cols on the same
> work sheet that calculates the prior years =A0OH$
> but when I cross check it doesn't seem to be right.
>
> Col B (Calculated) =A0has the following formula =A0 =3DG3/(1-(C3)*-1)
> Col C =A0=3D Contains =A0the OH %CHG for each Dept/Cat for that week
> COL D (Begins Pivot Table) =3D Contain Category Name
> COL E =3D DeptNum
> COL F =3D DeptName
> COL G (Being Data) =3D Date on Column headers, and value of Inventory Leve=
l
> The Problem occurs which I cross check the math.
> COL A Contains =3DSUM(B3:B13)
> If I summarize(COL A) the value in the range for each category (All Deptnu=
m
> in Category A for example it doesn't match)
> am I doing something wrong Mathematically or is the data possibly bad? =A0=
See
> below for data sample
> The issue is $104,007 with a (21.9)% chg =3D $133,182 not $126,785 the sum=
of
> the individual changes
> I have confirmed thet query data matches the database data
>
> Thanks for any Help
>
> A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0B =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0C =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0D
> E =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0F =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0G
> =A0 =A0 =A0LY =A0OnHand Retail$ =A0 =A0 DeptDate
> =A0 =A0 =A05/3/08 Diff Category Dept# DeptDesc 5/3/2008
> =A0 =A0 =A0 (row 3) $50,071 =A0-40.90% Consumables 110 Food $29,592
> =A0 =A0 =A0$909 =A071.90% =A0 111 DSD $1,563
> =A0 =A0 =A0$4,509 =A0-45.40% =A0 114 Speciality Food $2,462
> =A0 =A0 =A0$0 =A0890.00% =A0 115 Frozen Food
> =A0 =A0 =A0$43,248 =A0-18.90% =A0 120 HBC $35,074
> =A0 =A0 =A0$0 =A0 =A0 121 Control Sub
> =A0 =A0 =A0$5,110 =A0-8.30% =A0 130 House Keeping $4,686
> =A0 =A0 =A0$7,561 =A0-14.90% =A0 140 Household Chemicals $6,434
> =A0 =A0 =A0$4,811 =A0-14.30% =A0 150 Paper $4,123
> =A0 =A0 =A0$10,081 =A031.60% =A0 160 Pet $13,266
> =A0 =A0 =A0$6,883 =A0-1.10% =A0 170 Home Organization $6,807
> =A0 =A0 =A0 $133,182 =A0$126,785 =A0-21.90% Consumables Total =A0 =A0 $104=
,007

Kevin
Thu May 08 05:37:23 CDT 2008

Hi

Thanks for the reply , I changed the formulas to use PV/FV, since basically
that is what I am looking for. The cross check is pretty close ($200 or so
at times) but with the rounding factor that would be expected with #'s over
$100K. Your formula should give the same results. For some reason I
thought I had to do it the other.

Kevin

"joeu2004" <joeu2004@hotmail.com> wrote in message
news:795c63cc-1703-415b-bfa1-74bf392abcd5@a9g2000prl.googlegroups.com...
On May 7, 6:08 am, "Kevin Labore" <kev...@somewhere.net> wrote:
> The issue is $104,007 with a (21.9)% chg = $133,182
> not $126,785 the sum of the individual changes

I don't see how you compute $126,785 as "the sum of
individual changes". But I do note that $126,792 is
about 104007*(1-(-21.91%)), where the total declined
by 21.91% from $133,182 to $104,007.

I assume that is the gist of your mistake.

For example, if Food declined by 40.90% from $50,071
to $29,592, you can compute $29,592 by 50071*(1-40.90%).
You can compute $50,071 by 29592/(1-40.90%), not
29592*(1-(-40.90%)).

I suspect you used the sum of the latter to compute
"the sum of the individual changes".

But you wrote:
> Col B (Calculated) has the following formula
> =G3/(1-(C3)*-1)

That's a perverse way of writing simply =G3/(1+C3). That seems to be
correct, if G3 is $29,592 and C3 is -40.90%, using my example above.


----- original posting -----

On May 7, 6:08 am, "Kevin Labore" <kev...@somewhere.net> wrote:
> I have the following spreadsheet which I attempting to calculate the Prior
> Years Inventory Level in actual Retail $
> I have a database query that retrieves the following Information:(
> DeptDate( week end date(Saturday), OH Retail$, % Chg Prior Year), DeptNum
> DeptName, CatName
>
> (Dept = Abbreviation for Department)
> DeptDate = (the date(Saturday) week ending (i.e. 5/10/08_
> OH Retail$ = The Retail $ of Inventory for that department
> OH %Chg = % chg from Prior Year (Don't have access to Original Data for a
> certain Period of Time)
> Deptnum = Department #
> DeptName = Department Desc
> CatName = Category Name (where different sets of deptnums are grouped)
>
> The problem I am having is it doesn't seem to cross check the math.
> I have a pivot table to display the OH$ , I then have 2 cols on the same
> work sheet that calculates the prior years OH$
> but when I cross check it doesn't seem to be right.
>
> Col B (Calculated) has the following formula =G3/(1-(C3)*-1)
> Col C = Contains the OH %CHG for each Dept/Cat for that week
> COL D (Begins Pivot Table) = Contain Category Name
> COL E = DeptNum
> COL F = DeptName
> COL G (Being Data) = Date on Column headers, and value of Inventory Level
> The Problem occurs which I cross check the math.
> COL A Contains =SUM(B3:B13)
> If I summarize(COL A) the value in the range for each category (All
> Deptnum
> in Category A for example it doesn't match)
> am I doing something wrong Mathematically or is the data possibly bad? See
> below for data sample
> The issue is $104,007 with a (21.9)% chg = $133,182 not $126,785 the sum
> of
> the individual changes
> I have confirmed thet query data matches the database data
>
> Thanks for any Help
>
> A B C D
> E F G
> LY OnHand Retail$ DeptDate
> 5/3/08 Diff Category Dept# DeptDesc 5/3/2008
> (row 3) $50,071 -40.90% Consumables 110 Food $29,592
> $909 71.90% 111 DSD $1,563
> $4,509 -45.40% 114 Speciality Food $2,462
> $0 890.00% 115 Frozen Food
> $43,248 -18.90% 120 HBC $35,074
> $0 121 Control Sub
> $5,110 -8.30% 130 House Keeping $4,686
> $7,561 -14.90% 140 Household Chemicals $6,434
> $4,811 -14.30% 150 Paper $4,123
> $10,081 31.60% 160 Pet $13,266
> $6,883 -1.10% 170 Home Organization $6,807
> $133,182 $126,785 -21.90% Consumables Total $104,007

joeu2004
Thu May 08 12:05:57 CDT 2008

On May 8, 3:37 am, "Kevin Labore" <kev...@somewhere.net> wrote:
> I changed the formulas to use PV/FV, since basically
> that is what I am looking for.

If you want the percentage change, the formula should
be PV/FV - 1 (%change from FV to PV) or FV/PV - 1
(%change from PV to FV), formatted as Percentage. The
latter is consistent with the table you posted.

PV/FV and FV/PV yield muliplier factors (normally
formatted as Number or General), not percentage
change.


----- original posting -----

On May 8, 3:37=A0am, "Kevin Labore" <kev...@somewhere.net> wrote:
> Hi
>
> Thanks for the reply , I changed the formulas to use PV/FV, since basicall=
y
> that is what I am looking for. The cross check is pretty close ($200 or so=

> at times) but with the rounding factor that would be expected with #'s ove=
r
> $100K. =A0Your formula should give the same results. =A0For some reason I
> thought I had to do it the other.
>
> Kevin
>
> "joeu2004" <joeu2...@hotmail.com> wrote in message
>
> news:795c63cc-1703-415b-bfa1-74bf392abcd5@a9g2000prl.googlegroups.com...
> On May 7, 6:08 am, "Kevin Labore" <kev...@somewhere.net> wrote:
>
> > The issue is $104,007 with a (21.9)% chg =3D $133,182
> > not $126,785 the sum of the individual changes
>
> I don't see how you compute $126,785 as "the sum of
> individual changes". =A0But I do note that $126,792 is
> about 104007*(1-(-21.91%)), where the total declined
> by 21.91% from $133,182 to $104,007.
>
> I assume that is the gist of your mistake.
>
> For example, if Food declined by 40.90% from $50,071
> to $29,592, you can compute $29,592 by 50071*(1-40.90%).
> You can compute $50,071 by 29592/(1-40.90%), not
> 29592*(1-(-40.90%)).
>
> I suspect you used the sum of the latter to compute
> "the sum of the individual changes".
>
> But you wrote:
> > Col B (Calculated) has the following formula
> > =3DG3/(1-(C3)*-1)
>
> That's a perverse way of writing simply =3DG3/(1+C3). =A0That seems to be
> correct, if G3 is $29,592 and C3 is -40.90%, using my example above.
>
> ----- original posting -----
>
> On May 7, 6:08 am, "Kevin Labore" <kev...@somewhere.net> wrote:
>
>
>
> > I have the following spreadsheet which I attempting to calculate the Pri=
or
> > Years Inventory Level in actual Retail $
> > I have a database query that retrieves the following Information:(
> > DeptDate( week end date(Saturday), OH Retail$, % Chg Prior Year), DeptNu=
m
> > DeptName, CatName
>
> > (Dept =3D Abbreviation for Department)
> > DeptDate =3D (the date(Saturday) week ending (i.e. 5/10/08_
> > OH Retail$ =3D The Retail $ of Inventory for that department
> > OH %Chg =3D % chg from Prior Year (Don't have access to Original Data fo=
r a
> > certain Period of Time)
> > Deptnum =3D Department #
> > DeptName =3D Department Desc
> > CatName =3D Category Name (where different sets of deptnums are grouped)=

>
> > The problem I am having is it doesn't seem to cross check the math.
> > I have a pivot table to display the OH$ , I then have 2 cols on the same=

> > work sheet that calculates the prior years OH$
> > but when I cross check it doesn't seem to be right.
>
> > Col B (Calculated) has the following formula =3DG3/(1-(C3)*-1)
> > Col C =3D Contains the OH %CHG for each Dept/Cat for that week
> > COL D (Begins Pivot Table) =3D Contain Category Name
> > COL E =3D DeptNum
> > COL F =3D DeptName
> > COL G (Being Data) =3D Date on Column headers, and value of Inventory Le=
vel
> > The Problem occurs which I cross check the math.
> > COL A Contains =3DSUM(B3:B13)
> > If I summarize(COL A) the value in the range for each category (All
> > Deptnum
> > in Category A for example it doesn't match)
> > am I doing something wrong Mathematically or is the data possibly bad? S=
ee
> > below for data sample
> > The issue is $104,007 with a (21.9)% chg =3D $133,182 not $126,785 the s=
um
> > of
> > the individual changes
> > I have confirmed thet query data matches the database data
>
> > Thanks for any Help
>
> > A B C D
> > E F G
> > LY OnHand Retail$ DeptDate
> > 5/3/08 Diff Category Dept# DeptDesc 5/3/2008
> > (row 3) $50,071 -40.90% Consumables 110 Food $29,592
> > $909 71.90% 111 DSD $1,563
> > $4,509 -45.40% 114 Speciality Food $2,462
> > $0 890.00% 115 Frozen Food
> > $43,248 -18.90% 120 HBC $35,074
> > $0 121 Control Sub
> > $5,110 -8.30% 130 House Keeping $4,686
> > $7,561 -14.90% 140 Household Chemicals $6,434
> > $4,811 -14.30% 150 Paper $4,123
> > $10,081 31.60% 160 Pet $13,266
> > $6,883 -1.10% 170 Home Organization $6,807
> > $133,182 $126,785 -21.90% Consumables Total $104,007- Hide quoted text -=