weighted average

[Whitney]

I would like to calculate a weighted average for an overall score.

Example:
5 point scale
Quality - 3 weighted at 50%
Attendance - 4 weighted at 25%
Productivity
Hold Time - 2 weighted at 12.5%
Wrap Up - 3 weighted at 12.5%
Overall Score = average of the above
without weighting = 3
with weighting = ?
What formula can I use for the overall score to calculate a weighted average?

 

[Roger Govier]

Hi Whitney

With Values in B1:B4 and percentages in c1:C4
=SUMPRODUCT(B1:B4,C1:C4)
produces the result = 3.75

 

[Whitney]

What if I don't have the data in consecutive cell ranges?

For example:
Quality is cell b1
Attendance is cell b4
Hold is cell b5
Wrap up is cell b9

 

[Whitney]

I tried the formula as you indicated below, but came up with a different sum.

B C
1 3 50%
2 4 25%
3 2 12.5%
4 3 12.5%
= 3.125

 

[MartinW]

Hi Whitney,

3.125 is the correct answer.
Provided your weightings are not going to change
you may be able to approach it like this.
=SUM(B1*4,B4*2,B5,B9)/8

HTH
Martin

 

[Roger Govier]

Whitney
My apologies
I inadvertently typed in 50%, 25%, 25%, 25% as my values in column C (it
does help if the sum of %'s =100, doesn't it<bg>)

In answer to your other question about the cells not being contiguous, it
doesn't matter.
=SUMPRODUCT(B1:B9,C1:C9)

will work fine, provided there are no other Percentages in column C, other
than C1,C4,C5 and C9
Text or Numeric values in other cells in column B, will not affect the
result, nor will text in other cells of column C.

 

[Whitney]

EXCELLENT!!! It worked. Thanks so much for your help!