a goal seeking average formula

[Victtor]

In my industry we are graded on the average speed of service for our customers. Today we had an average of 225 seconds for 13 cars. My goal is 160 seconds. I want to know how many cars would I have to service at 95 seconds (catchup average) to reach my average goal of 160 seconds. (the answer is 13 cars at 95 seconds plus 13 cars at 225 seconds equals 26 cars at 160 seconds)

So, If I have in cell C12 the current average (225), and in cell C13 the target average (160), cell C14 the catchup average (95), and in cell the current # cars (13), what formula do I put in cell D14 to show how many cars at the catchup average will I have to do to reach my goal?

 

[OTOTO]

if cell C15 has the current # of cars, 13, then Your D14 is going to be equal to X where: ((C14+X)+(C12*C15))/(X+C15)=C13

 

[Victtor]

if cell C15 has the current # of cars, 13, then Your D14 is going to be equal to X where: ((C14+X)+(C12*C15))/(X+C15)=C13

Thanks OTOTO, can you write the formula exactly as I would put it in D14?

 

[shg]

	B​	C​	D​	E​
12​	Cur Avg​	225​	C12: Input
13​	Tgt​	160​	C13: Input
14​	New Avg​	95​	C14: Input
15​	Cars @ Cur Avg​	13​	C15: Input
16​	Cars at New Avg​	13​	C16: =C15*(C13-C12)/(C14-C13)
17​	Check​	160​	C17: =(C16*C12+C15*C14)/26

 

[Victtor]

	B​	C​	D​	E​
12​	Cur Avg​	225​	C12: Input
13​	Tgt​	160​	C13: Input
14​	New Avg​	95​	C14: Input
15​	Cars @ Cur Avg​	13​	C15: Input
16​	Cars at New Avg​	13​	C16: =C15*(C13-C12)/(C14-C13)
17​	Check​	160​	C17: =(C16*C12+C15*C14)/26

Thanks Shg, worked like a charm

Edit: Actually, the D17 check does not work when I put in other numbers. It only works in this scenario. (i.e. if I change my new average to 100, the "check" does not validate)

 

[shg]

Oops:

	B​	C​	D​
12​	Cur Avg​	225​	C12: Input
13​	Tgt​	160​	C13: Input
14​	New Avg​	100​	C14: Input
15​	Cars @ Cur Avg​	13​	C15: Input
16​	Cars at New Avg​	14.08​	C16: =C15*(C13-C12)/(C14-C13)
17​	Check​	160​	C17: =(C15*C12 + C16*C14) / (C15+C16)