Bug in polynomial interpolation?
Bug in polynomial interpolation?
(OP)
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).
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).





RE: Bug in polynomial interpolation?
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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Bug in polynomial interpolation?
If you can post the file somewhere, that'll be better for debugging.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Bug in polynomial interpolation?
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.
RE: Bug in polynomial interpolation?
You were trying to fit a 4th order polynomial, your precision should have been set accordingly.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Bug in polynomial interpolation?
curves are perfectly overlapping now, happy there is no real bug and can proceed with my analysis right away.
RE: Bug in polynomial interpolation?
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
RE: Bug in polynomial interpolation?
good luck and hope this helps!
-pmover
RE: Bug in polynomial interpolation?
Still not convinced that excel is the best method of curve fitting. I still use xymath.
Regards,
athomas236