growth formula

[allgeef]

I have a row of 12 cells A to L. I have a total number of 100. In cell A I have a number of 5, in cell L I have a number of 50. Is there a formula that can show growth from A to L or 5 to 50 in the cells between A and L (low to high) summing to my total of 100? Regards

[MrShorty]

Short but useless answer -- Yes, there is a formula (or combination of formulas) that can do what you want.

First step is to decide what kind of growth model you want -- linear, logarithmic, exponential, other. This is not my area of expertise, and you have not provided much detail, so I have no idea what this model should look like.
Second step is to program this model into Excel to compute the 12 values from 5 to 50.
Third step, if your model did not yield the correct sum, is to invoke Solver. Tell it to:
3a) Set target cell -- the cell with the summation
3b) To a value of -- 100
3c) by changing -- the parameters of the growth model.

There are undoubtedly other approaches that could be taken, but the big detail in any of them that spring to mind is to define the growth model.

[allgeef]

I would think exponential, but if there is a different formula for linear it would be nice to see what that is also.

[shg]

You have ten cells that need to total 45, so their values average 4.5, which is less than your starting value -- so no, not possible.

[MrShorty]

I don't know what you mean by "different". Standard linear formula: y=mx+b
standard exponential formula: y=b*exp(a*x) or, as used by the LOGEST() function y=b*m^x where m=exp(a).
or perhaps there are other forms of these equations that you are used to using?

Are the (1, 5) and (12, 50) points solid anchor points, or are they guidelines? Because these are two parameter equations, if these are fixed anchor points, they are sufficient to fix m and b and there is no "flexibility" left to find the sum=100. Perhaps you need an extension of those equations to so that you have 3 parameters? Again, this isn't exactly my field, so I am not certain what these equations should look like.

And shg adds a salient point, if the (1, 5) point is a fixed value. -- Unless the values between can extend below 5 -- but that isn't what one usually thinks of for "exponential growth" to have intermediate values that are less than either end point.

[allgeef]

Worded differently, I have a row of 12 cells A1 to L1. I have a number 100 in cell A2. In cell A1 I have a number of 5. I would like a formula in cells B1 to L1 that would trend upwards summing to the total of 100.

[newdoverman]

Solver can give a result that almost works using the constraints shown
Capture 100.JPG

[shg]

One way:

	A	B	C
1	Sum	100	B1: Input
2	Terms	12	B2: Input
3	First Term	5	B3: Input
4	Delta	0.606061	B4: =2*(B1 - B2*B3) / (B2*(B2-1))
5
6
7	Check
8	1	5.000	B8: =B3
9	2	5.606	B9: =B8+$B$4
10	3	6.212
11	4	6.818
12	5	7.424
13	6	8.030
14	7	8.636
15	8	9.242
16	9	9.848
17	10	10.455
18	11	11.061
19	12	11.667
20		100.000	B20: =SUM(B8:B19)

[newdoverman]

Here is another variation from Solver
Capture100-2.JPG

[allgeef]

This is ideal Guru. I have one challenge in that I keep coming up with .0505 for the Delta. Keeping after it to see where I'm inputting incorrectly.

[allgeef]

Got it right now Guru, thanks greatly.

[shg]

You're welcome.