Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scoring Formula helop | Excel Worksheet Functions | |||
Enabling voting button | Excel Discussion (Misc queries) | |||
weighted formula | Excel Discussion (Misc queries) | |||
Voting function | Excel Discussion (Misc queries) | |||
Voting results in Excel | Excel Discussion (Misc queries) |