Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate a weighted average in a pivot table? | Excel Discussion (Misc queries) | |||
Weighted Average in Pivot table | Excel Worksheet Functions | |||
weighted average on a pivot table | Excel Discussion (Misc queries) | |||
weighted average in pivot table | Excel Discussion (Misc queries) | |||
Weighted Average | Excel Discussion (Misc queries) |