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.
|