excel chart with trendline doesn't show the correct function

[koltachin]

I have a file with only four values for x & y when I make a chart and insert the trendline form +-x³ +- x²+- x +- cte the backsolving of the trendline for the values of x doesn't result in the expected value for y

any help is greatly appreciated
can give the file with the chart if needed

thanks in advance
Roger

 

[shg]

Post your data and the trendline equation you see.

 

[koltachin]

Post your data and the trendline equation you see.

graph 1
x	y
pace	lactate
24,00	2,00
23,00	2,70
22,00	4,10
16,70	10,00

<colgroup><col span="2"></colgroup><tbody>
</tbody>

the trend function

y=0,0542 x³-3.3885x²+68,781x-445,98 r²=1

but when I replace the x by 24 the resulting y value is not 2,0 but 2,2488

but when I replace the x by 23 the resulting y value is not 2,70 but 2,9179
but when I replace the x by 22 the resulting y value is not 4,10 but 4,2896

but when I replace the x by 16,70 the resulting y value is not 10,0 but 10,0784

thanks in advance

Roger

​

_{^{<strike>
</strike>}}
​

 

[shg]

	A​	B​	C​	D​	E​
1​	x​	y​	fit​	​	​
2​	24.00​	2.00​	2.00​		C2: =SERIESSUM(A2, 3, -1, $A$7:$C$7) + $D$7
3​	23.00​	2.70​	2.70​
4​	22.00​	4.10​	4.10​
5​	16.70​	10.00​	10.00​
6​
7​	0.054181​	-3.3885​	68.7813509​	-445.976​	A7:D7: {=LINEST(B2:B5, A2:A5^{1,2,3})}

 

[shg]

BTW, if you look at the trendline, which is a perfect fit as expected, you can see it would be useless for interpolation.

 

[Jon Peltier]

The problem is twofold.

1. The trendline equation in the chart is truncated to only 4 or 5 sig figs. If you format it using a scientific notation with lots of digits, your calculation will be closer to what you expect. Alternatively you could use LINEST as shg did without much explanation, and use those calculated coefficients without having to type them in.

2. More importantly, what shg also meant to say, is that you are overfitting your data. The chart below has two trendlines, a first order linear fit in blue and a third order polynomial fit in orange. Yours is the orange curve, which loops all around the data, but you'd be hard-pressed to describe why it's any better than the blue straight line. The slope and intercept of the blue line give Y estimates of 1.82, 2.94, 4.06, and 9.98 for your X values.

 

[koltachin]

thanks you all for the info
last question:
because I have only four or max five samples I need a function to extrapolate for another value of x ( x can be hart rate, speed(pace) or lactate)
IS there a way to get the coefficients of a function of the form x²+x+cte rather then overtyping then each time
i know the slope and intercept but that is for a linear function these are not linear function

kind regards
Roger

 

[joeu2004]

because I have only four or max five samples I need a function to extrapolate for another value of x ( x can be hart rate, speed(pace) or lactate)

It is generally a bad idea to extrapolate based on a polynomial trendline.

If we have "n" data points, we can always get a 100% fit (R^2=1) with a polynomial of n-1 degrees.

But the behavior of such a polynomial outside the data points (and sometimes even between the data points) can be radically different than what we might expect based on visual inspection.

The only time that extrapolation (and arguably interpolation) makes good sense is if the polynomial actually represents the natural behavior of the data model.

IS there a way to get the coefficients of a function of the form x²+x+cte rather then overtyping then each time

shg demonstrates how to use LINEST for that purpose in posting #4.

However, there are data sets for which LINEST does not behave as well as the chart trendline algorithm. I believe that happens because the input to LINEST is arrays of exponentially-scaled values, resulting in differences beyond the limits of 64-bit binary floating-point.

(Note: I am not talking about when LINEST and the chart trendline derive very different coefficients. That is possible and okay, as long as both predict about the same yhat.)

If the LINEST coefficients do not seem to work, it might be necessary and sufficient to copy the coefficients from the chart trendline label.

But if you do that, be sure to format the trendline label to display sufficient precision. I prefer to use the format Scientific with 14 decimal places. That's the maximum precision that Excel chooses to format; and it works equally well for all coefficients, regardless of magnitude.

-----

PS.... I see that both of those points were already covered by John Peltier. Klunk!

 

[Jon Peltier]

As Joe says:

It is generally a bad idea to extrapolate based on a polynomial trendline.

So be careful what formula you will use for extrapolating. You say it's not linear, but what you showed before was best approximated by a linear fit.

I'm also not sure what you're asking for. If you don't want to enter the trendline coefficients inaccurately time after time, use LINEST.

Microsoft's documentation:
https://support.office.com/en-us/article/linest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d

Slightly enhanced documentation:
http://www.mit.edu/~mbarker/formula1/f1help/04-g-m60.htm

Examples:
https://av8rdas.wordpress.com/2012/...tion-little-things-can-make-a-big-difference/
https://newtonexcelbach.com/2011/01/19/using-linest-for-non-linear-curve-fitting/

If you're just finding it tedious to retype the formula =A+B*x+C*x^2+D*x^3 in each cell, well, you should be entering the coefficients into separate cells, and point the formulas at these cells. For example, put your coefficients into cells D1:D4, put your X values into A3:A6, enter this formula into B3

=$D$1+$D$2*A3+$D$3*A3^2+$D$4*A3^3

and fill this down to B6. This way you only change the coefficients, not the formulas based on them.