Weighted Average - Urgent

[sachinattri]

Hey guys, problem will follow but wanted to take out a minute to let you
know that you are the best and this is best Excel resource for problems
or tips or tricks I have ever seen. Keep it up.

Now for the problem. I want to calculate weighted average

Group A- Music CDs
Province $
ON - 300 (60% of Group A)
QC - 200 (40% of Group A)
Total - 500

Group B- Music CDs
ON - 400 (80% of Group B)
QC - 100 (20% of Group B)
Total - 500

This is what I have, percentages within the Group. How do I calculate
the Weighted Average on the Totals of Group A and Group B (1000) to
know how much is the Weighted Average of ON on the Total Sales.

Thank you

Sach

 

[Paul]

Hi,

This should prove useful.
http://www.beyondtechnology.com/tips011.shtml

Paul

 

[Jason Morin]

Your weighted avg. formula on paper would look like (for
group A):

=300 x 60% + 200 x 40%

In Excel:

=300*60%+200*40%

Substitute each number with a cell reference.

HTH
Jason
Atlanta, GA

 

[sachinattri]

Thanks a lot Jason for replying promptly. I got 260 so that's th
weighted Number. how do I get a Weighted Percentage?

Thank

 

[serhat]

((0.6*300)+(0.8*400))/1000 Wtd % of A

((0.4*200)+(0.2*100))/1000 Wtd % of

 

[Bernie Deitrick]

Sach,

Actually, weighted average is meaningless in this context, since you don't
have any weights (such as prices).

You would actually just use
=(300 + 400)/(500 + 500)
to get an overall average.


If you were doing a weighted average like this

=(0.6 * 500 + 0.8 *500)/(500 + 500)

you would notice that the 0.6 is actually from 300/500, and the 0.8 is from
400/500. Substituting and simplying would simply give you my first formula.

HTH,
Bernie
MS Excel MVP

 

[AlfD]

Hi!

Let's take a step back.

First principles:

ON sells 300 Group A + 400 Group B = 700 total.

This is out of a total of 1000 sales, so the ON proportion is 700 ou
ot 1000, or 70%.

This was easy because we knew the actual numbers of sales (I'm assumin
they are sales) and because the base number was 500 in each group
there was no difference between the weighted average and the average.

Now change the numbers a bit:
Group A

ON | 400 | 80% (of 500)
QC | 100 |20%

Group B

ON | 600 | 40% (of 1500)
QC | 900 | 60%

The average of ON's _percentages_ is 60%. But that doesn't tak
account of the fact that its lowest % was achieved in the highes
"market". This is where the need for weighting comes in. You can'
simply average the percentages unless they are percentages of the sam
base figure.

The weighted result can be got by adding ON's numbers (=1000) an
expressing the sum as a percentage of the totals for A & B (2000). S
the true average is 50% in this case.

To generalise, the weighted average of two percentages p1 and p
derived from 2 base populations b1 and b2 is

(p1*b1 + p2*b2)/(b1+b2).

Al

 

[sachinattri]

Thanks again everyone. Some comments and concerns :

if I'm not mistaken Alf you are trying to do the same thing when yo
change the numbers what "serhat" has said. that is take

IstSaleIstGroup(300)*60%+IstSaleIIndGroup*80% / Total Sales

---------------------------data----------------------------
IstSale(ON)-300 | 60% IIndSale(QC)-200 | 40% | Total-500

IstSale(ON)-400 | 80% IIndSale(QC)-100 | 20% | Total-500
-----------------------------data---------------------------------------

but this gives a percentage of 50% and 10% equalling only 60%. th
total should be 100%

also would it make it easier if I say these are Total Portfolio Amount
of the Music Companies.

Thanks again

Sac

 

[AlfD]

Hi Sach!

Perish the thought!!

In the figures I invented, OR picked up 50% of the "all Groups" tota
of 2000, though it picked up 80% of Group A's 500 and 40% of Group B'
1500.

Similarly, QC picked up 20% of Group A's 500 and 60% of Group B's 150
giving it 50% of the total of 2000.
50+50=100.

Crucially: at no point can one multiply the percentage share by th
number which represents that share. It has no meaning, in reality or i
mathematical logic.

Al