Creating an algorithm or formula to generate a nonlinear increase in values between a specified range

[ddub25]

I am trying to create a table that can generate a nonlinear increase in values between 75% and 100%. I have attached a picture of my spreadsheet - the table on the left being the one that wants to generate percentage values for table rows 2,3 and 4 between 75% and 100%. The table to the right is an example of a linear increase in the percentage values (each increase in percentage is by 6.25%).

I have also attached a picture of what a graph may look like, showing nonlinear curve examples in orange as opposed to the linear line in white. I would like to be able to generate a natural, smooth curve shape of my choice by having the helper/input cells required to skew the curve in a nonlinear fashion and being able to bias the curve to fall off sharper to the end or sharper at the beginning. I want to be able to go through the process of testing different, nonlinear arcs and so will be creating a curve, testing it, creating another curve, testing it and so on.

If anyone can offer some help or point me in the right direction it would be greatly appreciated. Thanks

Dan W

 

[Sulprobil]

Take the LN or the EXP function with some stretching or shifting parameters, for example a*EXP(b*x+c)+d.

 

[Stephen W Allen]

This was an interesting challenge.

I took the exponent route rather than logs. While there must be other solutions which are equally valid, but this one seems to work.

Linear vs nonLinear increments

 

[Fluff]

@Stephen W Allen
Please post your solution to the board, rather than just posting a link to a file, as per Rule#4. Thanks

 

[Stephen W Allen]

Here is a mini sheet version of my possible solution, together with an image of the graphical output.

Linear_vs_NonLinear.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Linear and non-linear incrementing between 75% and 100%
2								5	Change the exponent value here.
3			Linear	Geo	InvGeo			Exponent	Lim25	InvExp	Lim25
4	75	0	75	75	75	1	0	0	0	0	0
5		1	78.125	75.00076	91.49385		3.125	1	0.000763	1	16.49385
6		2	81.25	75.02441	93.94646		6.25	32	0.024414	1.148698	18.94646
7		3	84.375	75.18539	95.5469		9.375	243	0.185394	1.245731	20.5469
8		4	87.5	75.78125	96.76376		12.5	1024	0.78125	1.319508	21.76376
9		5	90.625	77.38419	97.75705		15.625	3125	2.384186	1.37973	22.75705
10		6	93.75	80.93262	98.60219		18.75	7776	5.932617	1.430969	23.60219
11		7	96.875	87.82272	99.34118		21.875	16807	12.82272	1.475773	24.34118
12	100	8	100	100	100	25	25	32768	25	1.515717	25
13
14			Data feed to Graphical output.
15
16
Sheet1

Cell Formulas
Range	Formula
C4:C12	C4	=ShiftVal+G4
D4:D12	D4	=ShiftVal+I4
E4:E12	E4	=ShiftVal+K4
G4:G12	G4	=OldMin*B4*OldMax/IncrDiv
H4:H12	H4	=B4^MyExp
I4:I12	I4	=H4*25/MaxExp
J4:J12	J4	=B4^(1/MyExp)
K4:K12	K4	=J4*25/MaxInvExp

Named Ranges
Name	Refers To	Cells
IncrDiv	=Sheet1!$B$12	J12, G12:H12, G4:G11
MaxExp	=Sheet1!$H$12	I4:I12
MaxInvExp	=Sheet1!$J$12	K4:K12
MyExp	=Sheet1!$H$2	J4:J12, H4:H12
OldMax	=Sheet1!$F$12	G4:G12
OldMin	=Sheet1!$F$4	G4:G12
ShiftVal	=Sheet1!$A$4	C4:E12

 

[ddub25]

Thanks for the responses and yes, I thought it would be an interesting challenge. Stephen, I have tried to copy your table into Excel, but the formulas return #NAME? for me. Do I need an add in for excel for these formulas to work?

 

[ddub25]

For example, this formula,

=OldMin*B4*OldMax/IncrDiv

returns #NAME? when copied into my spreadsheet. Do I need an add in for excel for these formulas to work?

 

[Stephen W Allen]

I suspect you really need the original Excel spreadsheet, which handles names and formulae in the standard Excel manner. This can be downloaded from the link below.

Linear vs nonLinear increments

The chart above is not symmetrical about the straight line. You will get closer to symmetry around 2 axes (along the straight line and perpendicular to it) the the closer the exponent gets to the value of around 1.42. However the symmetry is not demonstrably explicit.

 

[ddub25]

Thanks Stephen - yes of course, they are Named Ranges and thanks for your help so far - it is greatly appreciated.

I have been playing around with the formulas, trying to get my head round how it works and have copied the table down and tried to enter different min and max values from which to generate nonlinear numbers between (as you can see in the Mini Sheet attached). I've tried to generate numbers between 76 (B22) and 86 (B26), but realised that this is not going to work considering the way the table has been designed to work. Is there a way to adapt the table so that it can generate numbers between random values that I enter? The numbers that I may want to enter will always be between 0 and 100.

Linear_vs_NonLinear.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Linear and non-linear incrementing between 75% and 100%
2									5	Change the exponent value here.
3				Linear	Geo	InvGeo			10	Lim25	InvExp	Lim25
4		75	0	75	75	75	1	0	0	0	0	0
5			1	78.125	75.00076	91.49385		3.125	1	0.000763	1	16.49385
6			2	81.25	75.02441	93.94646		6.25	32	0.024414	1.148698	18.94646
7			3	84.375	75.18539	95.5469		9.375	243	0.185394	1.245731	20.5469
8			4	87.5	75.78125	96.76376		12.5	1024	0.78125	1.319508	21.76376
9			5	90.625	77.38419	97.75705		15.625	3125	2.384186	1.37973	22.75705
10			6	93.75	80.93262	98.60219		18.75	7776	5.932617	1.430969	23.60219
11			7	96.875	87.82272	99.34118		21.875	16807	12.82272	1.475773	24.34118
12		100	8	100	100	100	25	25	32768	25	1.515717	25
13
14				Data feed to Graphical output.
15
16
17
18
19
20									5	Change the exponent value here.
21				Linear	Geo	InvGeo			10	Lim25	InvExp	Lim25
22	400	76	0	76	76	76	1	0	0	0	0	0
23	500		1	82	76	95		6.25	1	0.024414	1	18.94646
24	600		2	89	77	98		12.5	32	0.78125	1.148698	21.76376
25	700		3	95	82	100		18.75	243	5.932617	1.245731	23.60219
26	800	86	4	101	101	101	25	25	1024	25	1.319508	25
27
28				Data feed to Graphical output.
29
30
31
32
33
34
35
Sheet1

Cell Formulas
Range	Formula
D4:D12	D4	=ShiftVal+H4
E4:E12	E4	=ShiftVal+J4
F4:F12	F4	=ShiftVal+L4
H4:H12	H4	=OldMin*C4*OldMax/IncrDiv
I4:I12	I4	=C4^MyExp
J4:J12	J4	=I4*25/MaxExp
K4:K12	K4	=C4^(1/MyExp)
L4:L12	L4	=K4*25/MaxInvExp
D22:D26	D22	=ShiftVal_B+H22
E22:E26	E22	=ShiftVal_B+J22
F22:F26	F22	=ShiftVal_B+L22
H22:H26	H22	=OldMin_B*C22*OldMax_B/IncrDiv_B
I22:I26	I22	=C22^MyExp_B
J22:J26	J22	=I22*25/MaxExp_B
K22:K26	K22	=C22^(1/MyExp_B)
L22:L26	L22	=K22*25/MaxInvExp_B

Named Ranges
Name	Refers To	Cells
IncrDiv	=Sheet1!$C$12	K12, H12:I12, H4:H11
IncrDiv_B	=Sheet1!$C$26	K26, H26:I26, H22:H25
MaxExp	=Sheet1!$I$12	J4:J12
MaxExp_B	=Sheet1!$I$26	J22:J26
MaxInvExp	=Sheet1!$K$12	L4:L12
MaxInvExp_B	=Sheet1!$K$26	L22:L26
MyExp	=Sheet1!$I$2	K4:K12, I4:I12
MyExp_B	=Sheet1!$I$20	K22:K26, I22:I26
OldMax	=Sheet1!$G$12	H4:H12
OldMax_B	=Sheet1!$G$26	H22:H26
OldMin	=Sheet1!$G$4	H4:H12
OldMin_B	=Sheet1!$G$22	H22:H26
ShiftVal	=Sheet1!$B$4	D4:F12
ShiftVal_B	=Sheet1!$B$22	D22:F26

 

[Stephen W Allen]

In principle, you should be able to make those changes using the current Excel model. (I guess, from your response, that you have downloaded it.)

The model works by calculating the increments from 1 to a number stipulated by you. Then it shifts those increments to the point that you want in the range 1 to 100.

In my original model select the cell 'OldMax' (F12) and change it to 10 (ie 86 - 76). The select the cell 'ShiftVal' (A4) and change it to 76.

However, you should note the that model of the percentage points outside the range you have stipulated is NOT included.