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

 

[Stephen W Allen]

I wondered what sort of image a trigonometric curve would deliver. This is much more symmetrical, it does not deliver quite the style of curve in your original statement.

 

[ddub25]

Thanks Stephen, I overlooked the fact that the Lim25 columns were fixed to 25 (the sum of 100 – 75). I adapted the sheet to have OldMax automatically get its value and the Lim25 columns to reference OldMax. Now I can enter my range values and it works great so thanks very much for you help – it is greatly appreciated.

Is the trigonometric curve just a different way of representing graphically or are there different calculations within the tables? I am not to worried about the graphical output – I am just using that to get a visual understanding for myself of how nonlinear the values are and whether I need to make adjustments.

 

[Stephen W Allen]

The trig curve is different but not by much. Here is a mini sheet of the data points.

Linear_vs_NonLinear.xlsx
	A	B	C	D	E	F	G	H	I	J	K
2								7
3			Linear	Sin	Mirror Sin		Linear	Sin	Lim25	Mirror Sin	Lim25
4	75	0	75	75	75	1	0	0	0	0	0
5		1	78.125	83.80881	72.44119		3.125	0.276356	8.808812	5.683812	-2.55881
6		2	81.25	91.93151	70.56849		6.25	0.531186	16.93151	10.68151	-4.43151
7		3	84.375	98.73543	70.01457		9.375	0.744643	23.73543	14.36043	-4.98543
8		4	87.5	103.6906	71.30938		12.5	0.9001	28.69062	16.19062	-3.69062
9		5	90.625	106.4111	74.83888		15.625	0.98545	31.41112	15.78612	-0.16112
10		6	93.75	106.685	80.81496		18.75	0.994043	31.68504	12.93504	5.814962
11		7	96.875	104.491	89.25897		21.875	0.925212	29.49103	7.616035	14.25897
12	100	8	100	100	100	25	25	0.784316	25	0	25
Sheet2

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	=SIN(MyRad*B4/MaxTrig)
I4:I12	I4	=H4*25/MaxSin
J4:J12	J4	=I4-G4
K4:K12	K4	=G4-J4

Named Ranges
Name	Refers To	Cells
IncrDiv	=Sheet1!$B$12	G4:G12
MaxSin	=Sheet2!$H$12	I4:I12
MaxTrig	=Sheet2!$F$12	H4:H12
MyRad	=Sheet2!$H$2	H4:H12
OldMax	=Sheet1!$F$12	G4:G12
OldMin	=Sheet1!$F$4	G4:G12
ShiftVal	=Sheet1!$A$4	C4:E12

 

[ddub25]

Ok, thanks Stephen, I will compare the two when I get a chance. Thanks again for all your help - I am very grateful.

Dan W