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).

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

--


Regards,


Peo Sjoblom

"kalyan" wrote in message
...
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).

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


"kalyan" wrote in message
...
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,

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

Bernie


"Peo Sjoblom" wrote in message ...
=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)

--


Regards,


Peo Sjoblom

"kalyan" wrote in message
...
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).

Didn't even read that far, thanks <g

--


Regards,


Peo Sjoblom

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peo,

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

Bernie


"Peo Sjoblom" wrote in message
...
=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)

--


Regards,


Peo Sjoblom

"kalyan" wrote in message
...
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).

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" wrote:

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


"kalyan" wrote in message
...
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).

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$115 65)/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




"kalyan" wrote in message
...
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" wrote:

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


"kalyan" wrote in message
...
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).