Solving a Growth Formula

[tbeigi]

[TABLE="width: 907"]
<tbody>[TR]
[TD][/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sept[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD="align: left"]Ops[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]30[/TD]
[TD]35[/TD]
[TD]40[/TD]
[TD]45[/TD]
[TD]50[/TD]
[TD]275[/TD]
[/TR]
[TR]
[TD="align: left"]Case/Month [/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]16[/TD]
[TD]20[/TD]
[TD]24[/TD]
[TD]28[/TD]
[TD]32[/TD]
[TD]36[/TD]
[TD]40[/TD]
[TD]220[/TD]
[/TR]
[TR]
[TD="align: left"]Total Cases[/TD]
[TD]20[/TD]
[TD]80[/TD]
[TD]180[/TD]
[TD]320[/TD]
[TD]500[/TD]
[TD]720[/TD]
[TD]980[/TD]
[TD]1280[/TD]
[TD]1620[/TD]
[TD]2000[/TD]
[TD]7700[/TD]
[/TR]
</tbody>[/TABLE]

Above is the problem I am working on: We are assuming we can get 5 new operators per month for the year (beginning in March). Each operator would purchase 4 cases/month. The number of operators per month is variable - It could be 5 new operators per month for the year (Note: It won't change by MONTH - The number selected in March will carry on for the rest of the year) or it could be 6 new operators per month for the year. Also note, the numbers carry forward - May operator numbers accumulate the March and April numbers (thus giving 15 operators).

Cases/Month is a fixed number (4 cases/month/operator) - So if there are 12 operators in a month, then it would be 48 total cases.
Time range we are looking at is March - December (10 months).

I need to create a calculator so that someone can plug in the Operator # for March and a formula would instantly solve and give you the total cases (in this instance 7,700 cases). Can anyone help me with this? I desperately need assistance!

 

[jtakw]

Hello,

How did you come up with 1540, might I ask? I tried to get that from the formula, but my mind is a mess right now.

It's kinda hard to describe with words, for me anyways, but I'll try:

Your Ops and Case/Month are always incremented by the same amount in the following months, 5 and 4, respectively.
Total Cases is a product of the above, for April, the difference between Ops & Case/Month is 2, for May is 3, Jun is 4, Jul is 5, etc., etc.,
and since Total Cases is the product of the above, the multiplier for Total Cases is, 4 for April (2 x 2), 9 for May (3 x 3), 16 for June (4 x 4), 25 for July (5 x 5), etc., etc.,
You can confirm this by looking at Total Cases for March is 20, for April is 80 (4 times), for May is 180 (9 times), for June is 320 (16 times), for July is 500 (25 times), etc., etc.,
Total of multipliers for Total Cases for the year is 385, and since Case/Month is always a multiple of 4, 385 x 4 = 1540
Now you can plug any number in for Ops, multiply it by 1540, you'll get the correct result for Total Cases for the year.