|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 5th, 2008, 07:39 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
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? 
|
|
#2
February 6th, 2008, 12:26 AM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
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?
|
|
#3
February 6th, 2008, 12:50 AM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
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?
|
|
#4
February 6th, 2008, 12:57 AM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
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?
|
|
#5
February 6th, 2008, 08:33 AM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
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?
|
|
#6
February 6th, 2008, 10:27 AM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
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 itbg) 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?
|
|
#7
February 6th, 2008, 05:11 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
