weighted average
 

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?

weighted average
 

Hi Whitney

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

"Whitney" wrote in message
...
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?

weighted average
 

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

"Roger Govier" wrote:

Hi Whitney

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

"Whitney" wrote in message
...
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?

weighted average
 

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

"Roger Govier" wrote:

Hi Whitney

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

"Whitney" wrote in message
...
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?

weighted average
 

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


"Whitney" wrote in message
...
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

"Roger Govier" wrote:

Hi Whitney

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

"Whitney" wrote in message
...
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?

weighted average
 

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.
--
Regards
Roger Govier

"Whitney" wrote in message
...
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

"Roger Govier" wrote:

Hi Whitney

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

"Whitney" wrote in message
...
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?

weighted average
 

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

"Roger Govier" wrote:

Hi Whitney

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

"Whitney" wrote in message
...
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?