Weighted Average

[exceluser24]

So I need to find weighted average for Customer Satisfaction and Employee Efficiency with 50% Satisfaction and 50% Efficiency based on goals 80% satisfaction and 95% efficiency. I'm having troubles getting goals for satisfaction 'coz the range of that is -100% to 100%. Given 75% Satisfaction and 90% efficiency. I can get it by ( .75/.8 )*50 + ( .9 / .95 )*50. So how do I get average for -80% satisfaction?

 

[Luke M]

Formula would still be the same, answer comes out to be -3%

Here's the layout I made using your info

Formula to calculate weighted average:
=SUMPRODUCT(B2:C2/B3:C3,B4:C4)

I think the part that's confusing is that we're throwing in Goal as a factor. The way you have things, it's possible for final score to be anywhere from -115% to 115% (assuming negative/positive scores of 100%).

If this isn't what you want, we need to know what the expected range of the Average should be, which will let us adjust the algorithm.

 

[exceluser24]

Thanks. The formula will make my file more readable. But let's say % to goal should be from 0 to max when scores are at 100% (dont have excel right now). I was thinking of adjusting satisfaction to 1+actual to offset and moving the goal to 1+goal (so satisfaction will be 20% with goal of 180%).

 

[Luke M]

I think you've got the answer. To re-clarify, you've got 3 numbers with potential ranges:
Raw Score (two numbers)
Goal (two numbers)
Final Average (one number)

You've previously stated that the range of Raw Score can be -100 to +100. Are you wanting to change this?
What is the range of Goal? While it could be bad, does it numerically make sense to be negative? If Raw Score can be negative, then it makes sense that Goal could be negative.
That leaves us with Final average. If reader of your data is used to have a -100 to +100 scale, then we can leave the Final average as is. If the bottom of Average score is 0, then what does a 0 equate to? A Raw score of 0? Or a Raw score of -100?

 

[NARAYANK991]

Hi ,

Satisfaction is a measure which can range from -100% ( Absolute dissatisfaction ) to +100% ( Absolute satisfaction ).

Efficiency , however , can only range from 0% ( Absolute inefficiency ) to 100% ( Absolute efficiency ).

How the weighted average is to be calculated depends on the weights you will assign to the individual measures ; does a one point difference in satisfaction correspond to a one point difference in efficiency ? Does satisfaction derive from efficiency or vice-versa ? This is something which is domain dependent , and only you can say what it should be.

Thus a set of values such as 75% satisfaction and 90% efficiency can translate into any kind of resultant weighted value depending on the weights you have assigned.

Narayan