Solving a Growth Formula

[tbeigi]

	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec	Total
Ops	5	10	15	20	25	30	35	40	45	50	275
Case/Month	4	8	12	16	20	24	28	32	36	40	220
Total Cases	20	80	180	320	500	720	980	1280	1620	2000	7700

<tbody>
</tbody>

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!

 

[JohnGSnyder01]

If I am understanding you correctly then the following would work. I set up a spreadsheet like yours and the value for Ops for March in Cell B3 is 5, in the April value for Ops, cell C3 I put the formula =$B3+B3 & copied this formula over to the December column, cell K3. I tested this with a couple other values in cell B3 and the worksheet populated new values for Apr-Dec and a new total cases value; it was 9240 for 6 cases entered in March. Let me know if this works for you.

 

[tbeigi]

Hello,

Yes, that works, but what I need is a single formula that would calculate this for me without having to use the whole table of months. I would like it to look like this:

Operators: 5 (or any number/variable)
Total Cases: ## (Formula to solve for this using the information I've provided)

Is there anyway to do this? :/

Thank you!!!

If I am understanding you correctly then the following would work. I set up a spreadsheet like yours and the value for Ops for March in Cell B3 is 5, in the April value for Ops, cell C3 I put the formula =$B3+B3 & copied this formula over to the December column, cell K3. I tested this with a couple other values in cell B3 and the worksheet populated new values for Apr-Dec and a new total cases value; it was 9240 for 6 cases entered in March. Let me know if this works for you.

 

[MateusT]

Number of initial operators times 1540. It will always give you the sumof 10 months, where the increase of cases is 4 per month and the operators increase linearly by the rate of the first element.

 

[JohnGSnyder01]

That is a really cool solution. Can you clarify how that number was calculated? I came up with a long formula, which is shown below.

=(B13*4)+(2*B13)*8+(3*B13)*12+(4*B13)*16+(5*B13)*20+(6*B13)*24+(7*B13)*28+(8*B13)*32+(9*B13)*36+(10*B13)*40

 

[jtakw]

That is a really cool solution. Can you clarify how that number was calculated? I came up with a long formula, which is shown below.

=(B13*4)+(2*B13)*8+(3*B13)*12+(4*B13)*16+(5*B13)*20+(6*B13)*24+(7*B13)*28+(8*B13)*32+(9*B13)*36+(10*B13)*40

Actually I came up with the same solution as MateusT in post #4 right around the same time, but since it was already posted, I didn't, but now decided to post with sample:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2		Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec	Total
3	Ops	5	10	15	20	25	30	35	40	45	50	275
4	Case/Month	4	8	12	16	20	24	28	32	36	40	220
5	Total Cases	20	80	180	320	500	720	980	1280	1620	2000	7700		7700
Sheet1

Cell Formulas
Range	Formula
N5	=B3*1540

Look at the number pattern carefully, you'll see how 1540 is derived.

 

[tbeigi]

Number of initial operators times 1540. It will always give you the sumof 10 months, where the increase of cases is 4 per month and the operators increase linearly by the rate of the first element.

Hello,

I tried this with other Operator Numbers (i.e. 6) and the total cases were incorrect - Is there a formula that could potentially do this for me, so that it accounts for the variable operator number?

 

[tbeigi]

Hello,

Thank you for your reply! I appreciate it. Unfortunately, this only works if B3 = 5. B3 is meant to be a variable number, the following numbers in row 3 calculate depending on B3. (i.e. if B3=6, then C3 would be B3+6 and so on and so forth). Therefore, I need a formula that would account for the variable B3. Any ideas would be greatly appreciated!

 

[tbeigi]

Hello,

I apologize - I noticed a flaw in my original formula, but you are correct - 1540 works. Thank you!

Hello,

I tried this with other Operator Numbers (i.e. 6) and the total cases were incorrect - Is there a formula that could potentially do this for me, so that it accounts for the variable operator number?

 

[tbeigi]

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.

Actually I came up with the same solution as MateusT in post #4 right around the same time, but since it was already posted, I didn't, but now decided to post with sample:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2		Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec	Total
3	Ops	5	10	15	20	25	30	35	40	45	50	275
4	Case/Month	4	8	12	16	20	24	28	32	36	40	220
5	Total Cases	20	80	180	320	500	720	980	1280	1620	2000	7700		7700

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula N5 =B3*1540 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Look at the number pattern carefully, you'll see how 1540 is derived.