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
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Bidder A[/TD]
[TD][/TD]
[TD]Bidder B[/TD]
[TD][/TD]
[TD]Bidder C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Criteria
[/TD]
[TD]Weight[/TD]
[TD]Score[/TD]
[TD]Weighted
Score[/TD]
[TD]Score[/TD]
[TD]Weighted
Score[/TD]
[TD]Score[/TD]
[TD]Weighted
Score[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Criteria​

[/td][td="bgcolor:#F3F3F3"]

Weight​

[/td][td="bgcolor:#F3F3F3"]

Bidder A​

[/td][td="bgcolor:#F3F3F3"]

Bidder B​

[/td][td="bgcolor:#F3F3F3"]

Bidder C​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

A​

[/td][td]

10​

[/td][td]

8​

[/td][td]

3​

[/td][td]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

B​

[/td][td]

8​

[/td][td]

7​

[/td][td]

5​

[/td][td]

5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

C​

[/td][td]

9​

[/td][td]

4​

[/td][td]

4​

[/td][td]

7​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

D​

[/td][td]

6​

[/td][td]

7​

[/td][td]

3​

[/td][td]

4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

E​

[/td][td]

7​

[/td][td]

7​

[/td][td]

4​

[/td][td]

5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

F​

[/td][td]

5​

[/td][td]

1​

[/td][td]

5​

[/td][td]

6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

G​

[/td][td]

1​

[/td][td]

8​

[/td][td]

10​

[/td][td]

7​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Weighted:​

[/td][td="bgcolor:#E5E5E5"]

6.00​

[/td][td="bgcolor:#E5E5E5"]

4.07​

[/td][td="bgcolor:#E5E5E5"]

4.76​

[/td][td]C9: =SUMPRODUCT($B$2:$B$8, C2:C8) / SUM($B$2:$B$8)[/td][/tr]
[/table]

 

[Lesjoan01]

Hi SHG,

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