[Math Question] - Equation based on contribution to a weighted average

[Chris The Rock]

This is a tough question to know how to ask, but I'll do my best. I feel like the answer may be easy, but I'm working off of a really tired brain.

To start, I know how to calculate Weighted Averages in Excel using SUMPRODUCT. What I'm trying to determine are the upper bound of a particular scenario.

Let's say that 4 widget shops are surveyed for Customer Satisfaction.

For the Customer Satisfaction target, the owner has decided to use the Weighted Average of all of the Customer Satisfaction survey results from all the widget shops.

A bonus can be awarded to a widget shop whose scores are above the Weighted Average. The bonus is on a sliding scale, such that being very high above the Weighted Average will yield the best bonus.

The issue I'm trying to solve is this: One of those shops handles the majority of widget production. That means they will have the most contribution to the weighted average, making it difficult/impossible to have a Customer Satisfaction score that is very high above the weighted average.

I'm looking for an equation (and possibly a corresponding excel formula, if it's really complicated) to determine what's the maximum percentage above the weighted average one widget shop can be, based on their contribution to the weighted average.

For example:
North - 65% of Volume
South - 15%
East - 10 %
West - 10%

Weighted Average (x) = 94%

If the weighted average of all the Customer Satisfaction scores is x, what's the equation (and possibly the correspoding excel formula) to find the highest score above x (94%) North could possibly achieve?

I wish I had remembered more Algebra.

Thanks in advance to anyone who can guide me. I don't know what to call this operation, or I would have started with Google.

 

[shg]

I don't understand the question. North could have a 100% score, and the others could be lower to compensate.

If North has 100%, the weighted average must be at least his weight (65%).

 

[shg]

OTOH, if the weighted average is 94%, then North's score must be at least 90.8%, since the other 3 can contribute at most 35% to that average;

94% - 35% = 59%

59%/65% = 90.8%

 

[StephenCrump]

For North, WA (weighted average) = 65% OwnScore + 35% (WA of OtherScores)

So excess score above WA, i.e. OwnScore - WA

= 35% (OwnScore -WA of OtherScores).

So at best, if North scores 100%, its excess score will be:

= 35% (100 -WA of OtherScores).

So, for example:

C7: =SUMPRODUCT(B2:B5,C2:C5)
D2: =(1-B2)*(C2-(SUMPRODUCT(B$2:B$5,C$2:C$5)-B2*C2)/(SUM(B$2:B$5)-B2)) Copy down
E2: =(1-B2)*(100-(SUMPRODUCT(B$2:B$5,C$2:C$5)-B2*C2)/(SUM(B$2:B$5)-B2)) Copy down

Change C2 to 100, weighted average becomes 87.35, and North has outperformed WA by 12.65 (=E2)


Excel 2010

	A	B	C	D	E
North
South
East
West
WA

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Weight[/TD]
[TD="align: right"]Score[/TD]
[TD="align: right"]Excess above WA[/TD]
[TD="align: right"]Max possible excess[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]65%[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]2.15[/TD]
[TD="align: right"]12.65[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]15%[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]-0.85[/TD]
[TD="align: right"]27.20[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10%[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]21.15[/TD]
[TD="align: right"]31.05[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10%[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]-33.85[/TD]
[TD="align: right"]25.55[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]67.85[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[Chris The Rock]

I want to say thanks for the answers. I'm away from my pc until tomorrow, but I think the posts here are going to be great help.