Help requested for the mathematically illiterate

[WattsThat]

I'm trying to find the function for a graph. I thought I could do it in Excel with solver and while I can get a result, it's not even close. Just using the positive half of the graph, I assumed a quadratic form would give me something useful but it's not even close. I used y = a*x^2+ b*x. Was okay at the start but then diverged toward the end. So, it looks like it needs a polynomial buts there where I run out of guesses, its just not in my skill set.

Hopefully I've got the image and data attached correctly. The .txt file is actually a CSV so it can be imported into Excel or whatever tool you might use.

Thanks for any help you can provide.

[ataradov]

What error do you find acceptable?

Here is a simple Matlab script for polynomial fitting:

Code: [Select]

clear all;

d = dlmread('/tmp/2/data.txt');
x = d(51:end,1);
y = d(51:end,2);

p = polyfit(x, y, 3);

y1 = polyval(p, x);

figure; hold on;
plot(x, y); plot(x, y1);

For a 3rd degree polynomial for the positive side only it gives coefficients [-0.0002    0.0323   -0.4231    1.2352] with absolute maximum error of 1.24.

Note that it is much easier to fit one half of this curve. If you want to have the complete curve, the degree goes up. The closest result to the previous one os degree 10 with 0.98 error.

Also, if you exclude (0,0) point, then the fit is much better with [-0.0002    0.0330   -0.4573    1.6962], giving 0.8793 error.

[ledtester]

Here is an online polynomial fitter:

https://arachnoid.com/polysolve/

When pasting the data you can leave the commas -- it will treat it as a space/separator.

Entering just the pairs for x >= 0 and playing around with the degree setting yields:

Code: [Select]

Mode: normal x,y analysis
Polynomial degree 3, 51 x,y data pairs.
Correlation coefficient = 0.9998133198046751
Standard error = 0.4810384937464132

Output form: mathematical function:

f(x) =  1.2352223392164365e+000 * x^0
     + -4.2309645666560225e-001 * x^1
     +  3.2322906101639952e-002 * x^2
     + -1.8224862379502749e-004 * x^3


Copyright (c) 2019, P. Lutus -- http://arachnoid.com. All Rights Reserved.

[WattsThat]

Thank you,

I must be doing something wrong. Used the form

p(x) = ax^3 + bx^2 + cx + d

and for a b c d I used:  [-0.0002    0.0330   -0.4573    1.6962]

The x value of 100 generates an output of 86. I thought perhaps it was precedence in Excel but for the life of me, I cannot find the problem.

[TimFox]

Fitting a polynomial can give excellent results interpolating within the original domain (set of x-axis values).
Extrapolating outside that domain usually gives a turnover (where it would fall again in your example).

[ataradov]

Sorry, the accuracy of my numbers was not nearly enough. Try this [-0.000186159535457   0.033000052520777  -0.457300172655290   1.696223621363360]

Matlab number display sucks.

[T3sl4co1l]

A modified Laplacian seems to work well.  Try:

Code: [Select]

=$B$2*(1-1/(1+ABS(A11/$B$1)^$B$3))

For A11:A111 = -100:100 (x series), and B1 = 74.2, B2 = 146.72, B3 = 2.64.

This gives 11 zeroes across the range, an "unfairly" good fit for only 3 parameters.  It's still a bit worse than quantization noise, having a peak error around 0.8.

Possibly something with a trig function (say, a polynomial inside cos, or a polynomial in cos(x)), or some manner of special function (Excel does have Bessel functions) might give a more exact fit.

Tim

[WattsThat]

Wow! I am so impressed with this community. Thank you all. Now that I've recreated the data with a function, I'm embarrassed to admit that I've been looking in the wrong end of the microscope (an expression from a long ago departed co-worker). Read on if still you're still interested in helping the less capable.

I was able to get more than good enough results with the P.Lutus polynomial fit with a 6th order equation. I'm not sure what I was doing wrong in Excel with ataradov's data but that's okay for now, I'll come back to it - and while Tim's Excel file overwhelms me, I will chew on that when I'm not trying to put a fire out, which is what this exercise is all about. Tim's result is very impressive (thank you!) but all I'm trying to do is to come up with the ability to linearize a hydraulic proportional valve's output, that's what the curve is.

Now that I can recreate the data, I realize I've got it backwards because what I need is the inverse of the function. I need to pass in a value on the Y axis and come out with the X value.

Could someone point me in the correct direction? Yeah, I've been solving the wrong problem.

[WattsThat]

ataradov: Thank you for the additional effort. That data set is much better but I see that there is an inherent problem with using the lower order equation, things go wrong/negative in the bottom 20% of the curve.   

Still working the inverse function, which is what I really need.

[IanB]

Seems like you already have good help above, but one additional point is that Excel can often do the fitting for you. Just click on the data series and say "add trendline", then adjust the trendline options appropriately. See illustration below.

[WattsThat]

Seems like you already have good help above, but one additional point is that Excel can often do the fitting for you. Just click on the data series and say "add trendline", then adjust the trendline options appropriately. See illustration below.

Thank you, I had found that Excel feature earlier in the day and stopped at 3rd order. But for the life of me, I could not get Excel to duplicate the results with the displayed  equation. Interestedly, the original dataset that ataradov posted out of Matlab was exactly the same data that I got out of Excel for 3rd order. He subsequently updated his post with a better fit.

I'm still going in circles trying to figure out how to do an inverse function of what I've got - as that's what I need at this point.

Input the Y, get the X.

[calzap]

What is the origin of the data   … from real measurements, a model, or made-up?   It’s best to choose a type of equation for fitting that has some relationship to the mechanism that caused the y-variable to change when the x-variable changed.  For example, if the y-variable is the voltage output of an oscillator and the x-variable is time, a trigonometric function would make more sense than a polynomial.  Blindly fitting a polynomial is substituting an equation for the table of data, which can be handy, but may bear no more relationship to what is going on than the table itself.  And unless there is a mechanistic reason to believe otherwise, the polynomial shouldn’t used outside the range of data that was used to create it.

OTOH, if there is a good mechanistic reason to believe a particular type of equation describes the process and there is a good fit, then using the equation outside the range of the data may be justified.

Mike in California

[IanB]

I'm still going in circles trying to figure out how to do an inverse function of what I've got - as that's what I need at this point.

Input the Y, get the X.

That depends how much programming you want to do. You should say more about where and why you need this, because it will influence the answer. Even saying where the data came from and what it represents would influence the kind of function that would fit it. Polynomials rarely represent physical systems.

If you are on a computer, you could do simple linear interpolation between the points, or you could solve the y = f(x) equation numerically using Newton's method.

The inverse function does not fit well to a polynomial as a whole, but you could do a piece-wise fit over different portions of the curve.

There are so many options it is hard to give an answer. This kind of question is really hard to respond to, because you are not giving us any clues.

[ledtester]

I'm still going in circles trying to figure out how to do an inverse function of what I've got - as that's what I need at this point.

Input the Y, get the X.

One way is to just reverse the x-y pairs (i.e. make the x's the y's and the y's the x's) and get a polynomial fit.

Of course, you can only do this on a part of the function where it is either only increasing or only decreasing.

[T3sl4co1l]

There's probably a convenient formula inverting a polynomial, though I don't know of one offhand.  Would have to assume it's one-to-one, i.e. not kinked, and odd order so it doesn't double back on itself; then again, those problems sound very much like just another factorization problem...

More simply, just swap X and Y, plot the 0-100 part or whatever, and use the same curve fit operation.

Also, the LINEST function does the same thing at the cell level, as the curve fit does graphically; this is kind of a faff so I won't go into detail, but you can look up many excellent tutorials showing its use.  (The trickiest part is using the CTRL+SHIFT+ENTER array entry method, and remembering which cells in the result are which.)  This would just be of interest if you want more direct control over the input data, and using the output directly in other formulas.

The modified Laplace is easily inverted: try putting
=(1/(1-B11/$I$2)-1)^(1/$I$3)*$I$1
in cell L11 in the spreadsheet, and drag it down to fill the range.  Of course, this takes the positive result.

What is this going into, anyway?  If an embedded application (e.g. Arduino), the exponentiation and division will probably be quite slow.  Fine if you're only doing some hundreds or thousands of evaluations per second, but you'll need a more powerful microcontroller to do it much faster (one of the upper scale Arduinos, or, like, a rPi or something).  Polynomials typically excel on small MCUs so I would suggest that solution, if that's the case.

Oh, I didn't mention, the "even poly" is a fit (found using Solver) at double order, which is because it fits the whole curve (it's an even function, so an even order polynomial is needed; odd terms will just be noise).  This is about the limit of what's feasible for Solver to do; high order polynomials converge rather poorly (takes a long time to find a solution, or gives up completely), and need tricks to help it out.  The results should be similar to the regression, in any case.  (Which is the biggest trick of all: solving for the polynomial directly by a rather convenient bit of math.  Don't worry about reading that, just appreciate it's a "relatively" simple formula that Excel uses internally to solve the polynomial -- no need for the iterations and approximation that we are resorting to when using Solver!)

Tim

[WattsThat]

It will eventually be a lookup table in a PLC.

The answer was right in front of me, thank you ledtester for the website.

[Cerebus]

Looks like a distorted (peak compressed) cosine wave to me, and a quick manual curve fit seems to match my intuitions. Whatever this is, I suspect the underlying (pure) behaviour is [co]sinusoidal.

[IanB]

There's probably a convenient formula inverting a polynomial, though I don't know of one offhand.

I would lay odds you do know this one. A quadratic is inverted with the well known quadratic formula, a cubic can be done but the algebra is more complex, and for higher orders it is not worth the trouble. For anything above fourth order there is no algebraic solution.

Oh, I didn't mention, the "even poly" is a fit (found using Solver) at double order, which is because it fits the whole curve (it's an even function, so an even order polynomial is needed; odd terms will just be noise).

I think this is not true. It only needs to be "even" in the range of the fit. What it does outside that doesn't matter (if you do care, then any higher order polynomial will be a disaster). Within the range of the fit, every term can help.

[IanB]

It will eventually be a lookup table in a PLC.

I don't get it. If it's a lookup table, why do you want to fit an equation to it?

[WattsThat]

It will eventually be a lookup table in a PLC.

I don't get it. If it's a lookup table, why do you want to fit an equation to it?

I really needed the inverse function as I had to generate the data to put into the table somehow... All I had was the curve on paper and I wanted a 256 entry table. I suppose I could have generated it by hand, given the amount of time I spent on it. But, I did learn quite a few things along the way so it was a worthwhile effort.

[T3sl4co1l]

There's probably a convenient formula inverting a polynomial, though I don't know of one offhand.

I would lay odds you do know this one. A quadratic is inverted with the well known quadratic formula, a cubic can be done but the algebra is more complex, and for higher orders it is not worth the trouble. For anything above fourth order there is no algebraic solution.

Well... there would be that.   Well, that's for y=0 though.

Oh, right, just change the constant term c_n --> c_n - y.  That would do.

I think this is not true. It only needs to be "even" in the range of the fit. What it does outside that doesn't matter (if you do care, then any higher order polynomial will be a disaster). Within the range of the fit, every term can help.

Odd values can only ever subtract on the left side and add on the right, or vice versa; therefore they would give an increased error regardless of sign, except when zeroed.

For example, turning on the trendline and cranking it to 6th order, I get "y = -2E-12 x^6 + 6E-20 x^5 - 7E-07 x^4 + 4E-14 x^3 + 0.0177 x^2 + 1E-09 x - 1.7838", notice the alternating magnitudes with none of the odd orders being above ~1e-14 except the first at a similarly minuscule 1e-9.

This is different when fitting to just half the data, of course!

Tim

[IanB]

I really needed the inverse function as I had to generate the data to put into the table somehow... All I had was the curve on paper and I wanted a 256 entry table. I suppose I could have generated it by hand, given the amount of time I spent on it. But, I did learn quite a few things along the way so it was a worthwhile effort.

But you had the table already. You attached it to the first post? I'm still confused.

[T3sl4co1l]

Do you just want it interpolated to full scale e.g. ?

Code: [Select]

0	0
1	20
2	26
3	31
4	34
5	37
6	40
7	42
8	44
9	47
10	48
11	50
12	52
13	54
14	55
15	57
16	58
17	60
18	61
19	62
20	64
21	65
22	66
23	67
24	69
25	70
26	71
27	72
28	73
29	74
30	75
31	76
32	77
33	78
34	79
35	80
36	81
37	82
38	83
39	84
40	85
41	86
42	86
43	87
44	88
45	89
46	90
47	91
48	92
49	92
50	93
51	94
52	95
53	96
54	96
55	97
56	98
57	99
58	100
59	100
60	101
61	102
62	103
63	103
64	104
65	105
66	106
67	106
68	107
69	108
70	108
71	109
72	110
73	111
74	111
75	112
76	113
77	113
78	114
79	115
80	116
81	116
82	117
83	118
84	118
85	119
86	120
87	120
88	121
89	122
90	122
91	123
92	124
93	124
94	125
95	126
96	126
97	127
98	128
99	128
100	129
101	130
102	130
103	131
104	132
105	132
106	133
107	134
108	134
109	135
110	136
111	136
112	137
113	138
114	138
115	139
116	140
117	140
118	141
119	142
120	142
121	143
122	144
123	144
124	145
125	146
126	146
127	147
128	148
129	148
130	149
131	150
132	150
133	151
134	152
135	152
136	153
137	154
138	154
139	155
140	156
141	156
142	157
143	158
144	158
145	159
146	160
147	160
148	161
149	162
150	163
151	163
152	164
153	165
154	165
155	166
156	167
157	167
158	168
159	169
160	169
161	170
162	171
163	172
164	172
165	173
166	174
167	174
168	175
169	176
170	177
171	177
172	178
173	179
174	179
175	180
176	181
177	182
178	182
179	183
180	184
181	185
182	185
183	186
184	187
185	188
186	188
187	189
188	190
189	191
190	191
191	192
192	193
193	194
194	195
195	195
196	196
197	197
198	198
199	199
200	199
201	200
202	201
203	202
204	203
205	204
206	204
207	205
208	206
209	207
210	208
211	209
212	209
213	210
214	211
215	212
216	213
217	214
218	215
219	216
220	217
221	217
222	218
223	219
224	220
225	221
226	222
227	223
228	224
229	225
230	226
231	227
232	228
233	229
234	230
235	231
236	232
237	233
238	234
239	235
240	236
241	237
242	238
243	239
244	240
245	241
246	242
247	243
248	244
249	246
250	247
251	248
252	249
253	250
254	251
255	252

(...Would that be integer (byte?) format or float or what?  256 seems to suggest bytes, but I have no idea what your PLC is capable of...)

Tim

[Xandinator]

This could use https://en.wikipedia.org/wiki/Chebyshev_polynomials if it ought to approximate some kernelly stuff...