Bug in polynomial interpolation? 3

[Mccoy]

I'm aware polynomial interpolation has been discussed on these boards, I'm not sure about this specific topic though.

I have an excel 2002 SP-2 spreadsheet.
I've been trying for 2 days to interpolate a nth order polynomial curve to a data vector.

What happens is that the regression line automatically created on the spreadsheet graph fits perfectly the data.
Of course I'll verify that by use of the interpolation coefficients displayed by the same option.
Alas, the curve I build from the visualized equation coefficients is not the same as the one plotted.
The automatically plotted curve fits the data, whereas the curve manually plotted by myself, with coefficients which should be the same as the plotted curve, differs with that.
Specifically, I've just tried again with a 4th order polynomial the coefficients displayed by excel fit the curve all right until n = 10, for higher values the curve drifts evermore from the automatically plotted curve.
Is that a real bug or is there a way around it (I may end up installing specific add-ins).

 

[GregLocock]

The chances are that it is not really a bug, it is just lousy implementation.

So far as I can tell the significant figures of the displayed equation aren't set rationally.

SO, what you need to do is reformat the equation, to a more appropriate numerical format. Right click on the equation and set the number format appropriately.

Then you'll get the coefficients to sufficient accuracy.



Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[IRstuff]

However, in general, "curve fitting" measured data is usually a bad idea, since a polynomial fit will "fit" the measurement noise as well.

If you can post the file somewhere, that'll be better for debugging.

TTFN

FAQ731-376

 

[Mccoy]

Yes, attaching the file thru the engineering.com sharing link is a good idea indeed.

The file illustrates as blue dots the original y values as a function of x, as a black curve the 4th-order poly as interpolated by excel >>>>>> pretty good fit!

The inserted poly equation is also created by excel. Now, the red dots in the plot are the y-values back-calculated from the poly coefficients yielded by the excel 'visualize equation' option.
As it stands out, the red curve, which should exactly overlap the black curve, really does it only for x<=8.

The original x-y values are taken from a simple table and I need a function to implement in the spreadsheet.

 

[IRstuff]

As Greg intimated, your lack of precision is the culprit. You need to format the displayed fitting equation to the maximum precision (~17 decimal places) and then copy/paste the values from the displayed equation into your formulas. Then, the two curves will overlap.

You were trying to fit a 4th order polynomial, your precision should have been set accordingly.


TTFN

FAQ731-376

 

[Mccoy]

OK folks,


curves are perfectly overlapping now, happy there is no real bug and can proceed with my analysis right away.

 

[athomas236]

Try this

y = 2.46254E-05x^4 - 4.02004E-04x^3 - 6.43031E-04x^2 - 2.90267E-02^x + 9.86410E-01
R2 = 9.96137E-01

I just reformatted the display to scientific with 5 decimal places

 

[pmover]

fyi, the actual poly coefficients can be determined using the "linest" function in excel. see the attached workbook for a demonstration. the "linest" function must be entered as an array function.

good luck and hope this helps!
-pmover

 

[athomas236]

Take a start pmover, for unveiling the mysteries of linest; although it took me a while to figure the array formula formating.

Still not convinced that excel is the best method of curve fitting. I still use xymath.

Regards,

athomas236