Calculate Weighted Value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michele - 25 Sep 2007 17:52 GMT

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 to this Message

David Biddulph - 25 Sep 2007 18:03 GMT

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

Signature

David Biddulph

>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
[quoted text clipped - 18 lines]
> help
> would be greatly appreciated!

Reply to this Message

Michele - 25 Sep 2007 19:02 GMT

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?

> =SUMPRODUCT(B2:B4,C2:C4) gives the total in D5
>
[quoted text clipped - 22 lines]
> > help
> > would be greatly appreciated!

Reply to this Message

David Biddulph - 25 Sep 2007 19:31 GMT

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.

Signature

David Biddulph

> Thanks for the response David! I think I may have asked the question
> wrong,
[quoted text clipped - 52 lines]
>> > help
>> > would be greatly appreciated!

Reply to this Message