Formula for weighted voting/scoring

[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

 

[Bernard Liengme]

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

 

[PerryK]

Bernard,

Thanks much for the suggestion.
Could you explain what the formula is actually doing?

 

[Luke M]

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

 

[PerryK]

Luke,

Thanks for the explaination.

I think this will give me what I need.

 

[Bernard Liengme]

Go to Help and look at SUMPRODUCT
OR for more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes