weighted average from table
 

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.

weighted average from table
 

Maybe

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

Mike

"Cam" wrote:

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.

weighted average from table
 

Cam wrote:
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))

weighted average from table
 

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

"Cam" wrote:

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.

weighted average from table
 

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

"Cam" wrote:

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.