If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|