Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 113
Default Weighting a Percent of Change

Hi Everyone,

I need to weight my percent of change by province. E.g.

2004 2005 2005 Distribution % Change Weight
AB 182 268 6% 47% 2.8%
MB 1,285 1,295 29% 1% 0.2%
SK 784 887 20% 13% 2.6%
TOTAL 4255 4455 4.7% 5.7%

I see that MB has 29% of the market share, but only a % change of 1% in
volume.
AB has a small market share 6%, but is pulling the % change way up. But why
doesn't my weight formulas add up to total % change 4.7<5.7?
My weight formula is 2005 Distribution times % Change.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Weighting a Percent of Change

On Feb 8, 9:34*am, Jessica wrote:
I need to weight my percent of change by province. *E.g.
* * * * 2004 * *2005 * *2005 Distribution * * * % Change * * * *Weight
AB * * *182 * * 268 * * 6% * * * * * * * * * * * * * *47% * * * * * 2.8%
MB * * *1,285 * 1,295 * 29% * * * * * * * * * * * * * *1% * * * * * 0.2%
SK * * *784 * * 887 * * 20% * * * * * * * * * * * * * *13% * * * * *2.6%
TOTAL * 4255 * *4455 * * * * * * * * * * * * * * * * * 4.7% * * * * 5.7%

[....] why doesn't my weight formulas add up to total % change
*4.7<5.7?


Mostly because AB+MB+SK does not equal TOTAL. Either you have a
computation in those columns, or there are categories contributing to
TOTAL that you have not included in the table above.

Oh, I think I see your problem: your TOTAL includes the year title,
2004 and 2005 respectively. The correct totals are 2251 and 2450. So
you total change is about 10.9%.

Of course, changing the totals affects the "distribution" (proportion)
and weighted averages. But using the numbers above, it appears that
your "distribution" and "weight" formulas are correct.

So if you fix the TOTAL formula, I think everything will work out --
especially if you fix the format so that it consistently shows tenths
of a percent.

HTH.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 113
Default Weighting a Percent of Change

Ok, i fixed the totals.

2004 2005 2005 Distribution % Change Weight
AB 182 268 11% 47% 5.2%
MB 1,285 1,295 53% 1% 0.4%
SK 784 887 36% 13% 4.8%
TOTAL 2251 2450 8.8% 10.3%

8.8% still does not equl the sum of the weighted percentages 10.3%
Is should equal my total % change right?

"joeu2004" wrote:

On Feb 8, 9:34 am, Jessica wrote:
I need to weight my percent of change by province. E.g.
2004 2005 2005 Distribution % Change Weight
AB 182 268 6% 47% 2.8%
MB 1,285 1,295 29% 1% 0.2%
SK 784 887 20% 13% 2.6%
TOTAL 4255 4455 4.7% 5.7%

[....] why doesn't my weight formulas add up to total % change
4.7<5.7?


Mostly because AB+MB+SK does not equal TOTAL. Either you have a
computation in those columns, or there are categories contributing to
TOTAL that you have not included in the table above.

Oh, I think I see your problem: your TOTAL includes the year title,
2004 and 2005 respectively. The correct totals are 2251 and 2450. So
you total change is about 10.9%.

Of course, changing the totals affects the "distribution" (proportion)
and weighted averages. But using the numbers above, it appears that
your "distribution" and "weight" formulas are correct.

So if you fix the TOTAL formula, I think everything will work out --
especially if you fix the format so that it consistently shows tenths
of a percent.

HTH.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Weighting a Percent of Change

On Feb 8, 10:09*am, Jessica wrote:
Ok, i fixed the totals.

* * * * 2004 * *2005 * *2005 Distribution * * * % Change * * * *Weight
AB * * *182 * * 268 * * 11% * * * * * * * * * * 47% * * 5.2%
MB * * *1,285 * 1,295 * 53% * * * * * * * * * * *1% * * 0.4%
SK * * *784 * * 887 * * 36% * * * * * * * * * * *13% * *4.8%
TOTAL * 2251 * *2450 * * * * * * * * * * * * * * 8.8% * 10.3%

8.8% still does not equl the sum of the weighted percentages 10.3%
Is should equal my total % change right?


First, sorry for saying the total change was 10.9%. Silly misreading
of the calculator.

And sorry for not recognizing your other mistake initially: you want
to look at the proportion ("distribution") of the 2004 numbers, not
the 2005 numbers. For example, for AB, instead of 268/2450, compute
182/2251.

As for the reason why.... The way my wife explains it might make the
best sense: since the base for the %Change is the 2004 numbers, the
base for the proportion should be the 2004 numbers.

If that works for you, great. Stop here.

I need to look at it mathematically, but I arrive at the same
conclusion. For the total %Change, we compute (b1+b2+b3)/(a1+a2+a3) -
1, where the "b's" are 2005 and the "a's" are 2004. For the
individual %Change, we compute b1/a1, for example. So in order for
the sum of the weighted averages to equal the total %Change, we need
to have a1+a2+a3 in the denominator. Therefore, we multiply b1/a1 - 1
by a1/(a1+a2+a3). The "a1's" cancel, giving us b1/(a1+a2+a3) - a1/
(a1+a2+a3). When we sum that "b1 term" with the b2 and b3 terms, we
do indeed get (b1+b2+b3)/(a1+a2+a3) - 1.

Whew! That should be clear as mud ;-). Now go back and read my
wife's explanation :-).

HTH.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Percent change between two worksheets on same spreadsheet Thank you, Heath Excel Discussion (Misc queries) 9 May 3rd 07 05:51 PM
Percent change cardfan057 Excel Discussion (Misc queries) 4 May 10th 06 04:29 PM
Percent Change Ralph D via OfficeKB.com Excel Discussion (Misc queries) 1 December 8th 05 09:18 PM
Percent Change Tom B via OfficeKB.com Excel Discussion (Misc queries) 4 July 29th 05 04:42 PM
percent change stephenm Excel Worksheet Functions 5 July 19th 05 01:33 AM


All times are GMT +1. The time now is 05:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"