Weighted Averages for Rankings

[katie]

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.

 

[Bernie Deitrick]

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