Calculate Weighted Value

[Michele]

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!

 

[David Biddulph]

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

 

[Michele]

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]

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.