weighted mean

[kalyan]

is there any way to calculate the weighted mean in excel?
e.g weighted mean from A2:A4 = (B2*C2+B3*C3+B4*C4)/(B2+B3+B4)

this is just an example, i have a huge data of thre columns `A, B and C`. i
need to calculate the weighted mean of column C WITH column B at different
ranges matching with column A, such as for all the same values of column A
(SAY 44) calculate the wiehted mean of corresponding values of column C (12,
4, 5,8) with B (3, 9, 5, 6).

 

[Peo Sjoblom]

=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)

--


Regards,


Peo Sjoblom

 

[Bernie Deitrick]

kalyan,

This will give the weighted average of the values in C (weights in B) for those values where column
A is equal to 44: change all instances of 4000 to the actual last row.

=SUMPRODUCT((A2:A4000=44)*B2:B4000*C2:C4000)/SUMIF(A2:A4000,44,B2:B4000)

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Peo,

You overlooked the criteria that the values in column A be the same....

Bernie

 

[Peo Sjoblom]

Didn't even read that far, thanks <g>

--


Regards,


Peo Sjoblom

 

[kalyan]

Bernie
i didn`t understant what do you mean by ``change all instances of 4000 to
the actual last row``

I need to calculate the weighted mean of the values of column C (WEIGHTS
IN B) for all the same values of A (44 was just an example, there are more
than 10000 same values in column A , say 49 in column A having 10 different
values in column B and C; 59 in column A having seven different values in
coulumn B and C etc.). Its a big data and everytime i cannot put the formula,
A=44 OR A=49......ETC.

 

[Bernie Deitrick]

kalyan,

I meant that if your last data point is in row 11,565 then you need to
change the formula from

=SUMPRODUCT((A2:A4000=44)*B2:B4000*C2:C4000)/SUMIF(A2:A4000,44,B2:B4000)

to

=SUMPRODUCT((A2:A11565=44)*B2:B11565*C2:C11565)/SUMIF(A2:A11565,44,B2:B11565)

But you have a different concern. First, select column A, then use Data
Filter... Advanced Filter check "Unique Values Only" and select a cell,
let's say E2, for the destination. Then you will get a list of the unique
values in column A. Then in, say, F2, use the formula

=SUMPRODUCT((A$2:A$11565=E2)*B$2:B$11565*C$2:C$11565)/SUMIF(A$2:A$11565,E2,B$2:B$11565)

Again, change all of the 11565 values to the actual row number.....

Then copy F2 down to match the list in column E.

HTH,
Bernie
MS Excel MVP