Weighted average

[Lesjoan01]

Hi,

I have a spreadsheet that I need to calculate a weighted score for each entered criteria - See table below.

Each bidder will be given a score out of 10 for each of the criteria. Each criterion will be given a 'weighting' which is a multiplier for the score. This will then give each bidder a weighted score on each of the criteria. The bottom row simply gives the sum total of the scores and the weighted scores. I am struggling with the formula, I have tried SUMPRODUCT but I think I am getting it wrong - can only please help

Thank you very much

		Bidder A		Bidder B		Bidder C
Criteria	Weight	Score	Weighted Score	Score	Weighted Score	Score	Weighted Score
A	10
B	8
C	9
D	6
E	7
F	5
G	1
TOTAL

<tbody>
</tbody>

 

[Lesjoan01]

Just to provide detail of the formula I did use

=sumproduct(b3,c3)/sum(b3:c3)

which gave a weighted score of 3.43 for Bidder A under Delay criteria

Have I got that right?

Thanks very much

Lesley

 

[shg]

	A​	B​	C​	D​	E​	F​
1​	Criteria​	Weight​	Bidder A​	Bidder B​	Bidder C​	​
2​	A​	10​	8​	3​	2​
3​	B​	8​	7​	5​	5​
4​	C​	9​	4​	4​	7​
5​	D​	6​	7​	3​	4​
6​	E​	7​	7​	4​	5​
7​	F​	5​	1​	5​	6​
8​	G​	1​	8​	10​	7​
9​		Weighted:​	6.00​	4.07​	4.76​	C9: =SUMPRODUCT($B$2:$B$8, C2:C8) / SUM($B$2:$B$8)

 

[Lesjoan01]

Hi SHG,

thank you very much for your help with this - it is very much appreciated