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. |
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. |
All times are GMT +1. The time now is 09:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com