Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Weighted Value
I have an RFP I am working on. I am scoring 3 different companies on a
variety of areas. Each area is weighted a certain percentage value. For each area, I will give a score of 1-5 (5 being the greatest). I need a formula to calculate the percentage for each area based on the score given and the weighted percentage assigned. For example, the spreadsheet would look like this: A B C D 1 Area Weight Score Weighted 2 Hours 65% 3 3 Reporting 15% 4 4 Staff 20% 3 5 Total 100% 10 So what I need to figure out, is how do I calculate the value for column D based on the score given against the assigned weight of each area? Any help would be greatly appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Weighted Value
=SUMPRODUCT(B2:B4,C2:C4) gives the total in D5
As an alternative, if you want to you can put =B2*C2 in D2, copy down rows 2 to 4, and add. -- David Biddulph "Michele" wrote in message ... I have an RFP I am working on. I am scoring 3 different companies on a variety of areas. Each area is weighted a certain percentage value. For each area, I will give a score of 1-5 (5 being the greatest). I need a formula to calculate the percentage for each area based on the score given and the weighted percentage assigned. For example, the spreadsheet would look like this: A B C D 1 Area Weight Score Weighted 2 Hours 65% 3 3 Reporting 15% 4 4 Staff 20% 3 5 Total 100% 10 So what I need to figure out, is how do I calculate the value for column D based on the score given against the assigned weight of each area? Any help would be greatly appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Weighted Value
Thanks for the response David! I think I may have asked the question wrong,
though. Column D will be a percentage. What I need to figure out is what percentage Column C is of Column B. For example, if one area labled "Hours" is weighted 10% and is scored a 5 (out of 5 possible points), Column D should be 10%. So it would look like this: A B C D 1 Area Weight Score Weighted 2 Hours 10% 5 10% However, if "Hours" received a score of 2.5, column D would be 5%. It would then look like this: A B C D 1 Area Weight Score Weighted 2 Hours 10% 2.5 5% Does that make sense? Do you have any thoughts? "David Biddulph" wrote: =SUMPRODUCT(B2:B4,C2:C4) gives the total in D5 As an alternative, if you want to you can put =B2*C2 in D2, copy down rows 2 to 4, and add. -- David Biddulph "Michele" wrote in message ... I have an RFP I am working on. I am scoring 3 different companies on a variety of areas. Each area is weighted a certain percentage value. For each area, I will give a score of 1-5 (5 being the greatest). I need a formula to calculate the percentage for each area based on the score given and the weighted percentage assigned. For example, the spreadsheet would look like this: A B C D 1 Area Weight Score Weighted 2 Hours 65% 3 3 Reporting 15% 4 4 Staff 20% 3 5 Total 100% 10 So what I need to figure out, is how do I calculate the value for column D based on the score given against the assigned weight of each area? Any help would be greatly appreciated! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Weighted Value
If you're looking at the score out of your maximum of 5, change =B2*C2 to
=B2*C2/5, and format as percentage. Note that this isn't "what percentage Column C is of Column B", but it seems to be what you want. -- David Biddulph "Michele" wrote in message ... Thanks for the response David! I think I may have asked the question wrong, though. Column D will be a percentage. What I need to figure out is what percentage Column C is of Column B. For example, if one area labled "Hours" is weighted 10% and is scored a 5 (out of 5 possible points), Column D should be 10%. So it would look like this: A B C D 1 Area Weight Score Weighted 2 Hours 10% 5 10% However, if "Hours" received a score of 2.5, column D would be 5%. It would then look like this: A B C D 1 Area Weight Score Weighted 2 Hours 10% 2.5 5% Does that make sense? Do you have any thoughts? "David Biddulph" wrote: =SUMPRODUCT(B2:B4,C2:C4) gives the total in D5 As an alternative, if you want to you can put =B2*C2 in D2, copy down rows 2 to 4, and add. -- David Biddulph "Michele" wrote in message ... I have an RFP I am working on. I am scoring 3 different companies on a variety of areas. Each area is weighted a certain percentage value. For each area, I will give a score of 1-5 (5 being the greatest). I need a formula to calculate the percentage for each area based on the score given and the weighted percentage assigned. For example, the spreadsheet would look like this: A B C D 1 Area Weight Score Weighted 2 Hours 65% 3 3 Reporting 15% 4 4 Staff 20% 3 5 Total 100% 10 So what I need to figure out, is how do I calculate the value for column D based on the score given against the assigned weight of each area? Any help would be greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate weighted average of stock cost | Excel Worksheet Functions | |||
Weighted Avg | Excel Discussion (Misc queries) | |||
Non zero weighted average | Excel Worksheet Functions | |||
Can you calculate "weighted average cost of capital? | Excel Discussion (Misc queries) | |||
Weighted Average | Excel Discussion (Misc queries) |