Formula for weighted voting/scoring

  • Thread starter Thread starter PerryK
  • Start date Start date
P

PerryK

I am trying to create a formula for weighted voting/scoring.

I have 5 scoring catagories.

I will call the catagories a, b, c, d, e.
Each catagory could recieve a score of 1 - 10.

Catagory A should account for 35% of the total score.
Catagory B should account for 35% of the total score.
Catagory C should account for 20% of the total score.
Catagories D, E should each account for 5% total score.

thanks for any help with this
 
Let's say your category 'a' scores are listed in column A, the 'b' scores in
B, etc
In G1 thru K1 enter the weights 35%,35%,20%.5%,5%
In L1 enter text "Total"
In G2 enter =SUM(A:A)
Copy this across the row as far as K2, to sum each category
In L2 use =SUMPRODUCT(G1:K1,G2:K2) to find the weighed score

best wishes
 
Bernard,

Thanks much for the suggestion.
Could you explain what the formula is actually doing?
 
Your first row is entered as percentages, so that accounts for the weighting,
while row 2 is simply the sum of each category.

SUMPRODUCT multiples across the arrays (array 1 = row 1, array 2 = row 2),
and then sums those products. So, mathematically its G1*G2+H1*H2+I1*I2...
 
Back
Top