weighted average from table

[Cam]

Please help!

Here is the situation:
I need to deternime a weighted average of a set of values for a given
period. I have setup an example below:

period Col 2 Col 3
1 5 100
1 7 200
2 9 100
2 7 200

what would be the best formula to determine the weighted average value of
column 3 weighted on the basis of column 2 by period?

Any help would be appreciated.

 

[Mike H]

Maybe

=SUMPRODUCT((A1:A4=1)*(B1:B4*C1:C4))/SUMPRODUCT((A1:A4=1)*(B1:B4))

Mike

 

[smartin]

Please help!

Here is the situation:
I need to deternime a weighted average of a set of values for a given
period. I have setup an example below:

period Col 2 Col 3
1 5 100
1 7 200
2 9 100
2 7 200

what would be the best formula to determine the weighted average value of
column 3 weighted on the basis of column 2 by period?

Any help would be appreciated.

Or, referencing a cell that contains the period value,

=SUMPRODUCT(($A$1:$A$4=$A1)*($B$1:$B$4*$C$1:$C$4))/SUMPRODUCT(($A$1:$A$4=$A1)*($B$1:$B$4))

 

[Shane Devenshire]

Hi,

If you can tolerate an array formula then:

=SUM((A1:A4=1)*(B1:B4*C1:C4)/SUM(B1:B4*(A1:A4=1)))

Array entered - Shift+Ctrl+Enter instead of Enter.

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi,

I see there is one additional minor simplification to my previous post

=SUM((A1:A4=1)*B1:B4*C1:C4/SUM(B1:B4*(A1:A4=1)))

Still array entered.

Cheers,
Shane Devenshire