Formula for weighted voting/scoring
 

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




--
Perry K

Formula for weighted voting/scoring
 

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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"PerryK" wrote in message
...
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




--
Perry K

Formula for weighted voting/scoring
 

Bernard,

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


--
Perry K


"Bernard Liengme" wrote:

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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"PerryK" wrote in message
...
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




--
Perry K

Formula for weighted voting/scoring
 

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PerryK" wrote:

Bernard,

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


--
Perry K


"Bernard Liengme" wrote:

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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"PerryK" wrote in message
...
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




--
Perry K

Formula for weighted voting/scoring
 

Luke,

Thanks for the explaination.

I think this will give me what I need.
--
Perry K


"Luke M" wrote:

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PerryK" wrote:

Bernard,

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


--
Perry K


"Bernard Liengme" wrote:

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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"PerryK" wrote in message
...
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




--
Perry K

Formula for weighted voting/scoring
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"PerryK" wrote in message
...
Bernard,

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


--
Perry K


"Bernard Liengme" wrote:

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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"PerryK" wrote in message
...
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




--
Perry K