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!

 

[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
Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec	Total
Ops
Case/Month
Total Cases

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

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

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

[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]20[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]720[/TD]
[TD="align: right"]980[/TD]
[TD="align: right"]1280[/TD]
[TD="align: right"]1620[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]7700[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7700[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N5[/TH]
[TD="align: left"]=B3*1540[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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