Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted Averages for Rankings
I have a spreadsheet where we rank performance of 9 groups with 13 categories
of items. I want to put certian weight on certian items. Is there a way to do this and be able to rank the groups 1-9. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted Averages for Rankings
Katie,
Weighted scores are calculated using =SUMPRODUCT(Scores,Weights)/SUM(Weights) (though the /SUM(Weights) parts is optional if there is no requirement to keep the score within certain bounds.) You could use that formula for each group: =SUMPRODUCT(CategoryScores1,CategoryWeights)/SUM(CategoryWeights) =SUMPRODUCT(CategoryScores2,CategoryWeights)/SUM(CategoryWeights) ..... =SUMPRODUCT(CategoryScores9,CategoryWeights)/SUM(CategoryWeights) Depending on your layout, you will end up with something like this =SUMPRODUCT(A2:M2,$A$1:$M$1)/SUM($A$1:$M$1) or this =SUMPRODUCT(B2:B14,$A$2:$A$14)/SUM($A$2:$A$14) Then you can use RANK on those results =RANK(N2,$N$2:$N$10) HTH, Bernie MS Excel MVP "katie" wrote in message ... I have a spreadsheet where we rank performance of 9 groups with 13 categories of items. I want to put certian weight on certian items. Is there a way to do this and be able to rank the groups 1-9. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to omit blank cells when calculating weighted averages | Excel Worksheet Functions | |||
Weighted Averages? | Excel Worksheet Functions | |||
weighted averages in a pivot table - how? | Excel Worksheet Functions | |||
Calculating weighted averages | Excel Discussion (Misc queries) | |||
Weighted Averages in Excel | Excel Discussion (Misc queries) |