a goal seeking average formula

Victtor

Board Regular
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?

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

OTOTO

Board Regular
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

Board Regular
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

MrExcel MVP
 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

Board Regular
 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)

Last edited:

shg

MrExcel MVP
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)

Replies
8
Views
393
Replies
0
Views
372
Replies
1
Views
234
Replies
2
Views
384
Replies
5
Views
187

1,196,007
Messages
6,012,825
Members
441,731
Latest member
jonceramic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back