×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Bug in polynomial interpolation?
3

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).

RE: Bug in polynomial interpolation?

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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Bug in polynomial interpolation?

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: Eng-Tips.com Forum Policies

RE: Bug in polynomial interpolation?

(OP)
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.

RE: Bug in polynomial interpolation?

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: Eng-Tips.com Forum Policies

RE: Bug in polynomial interpolation?

(OP)
OK folks,
thumbsup2

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

RE: Bug in polynomial interpolation?

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

RE: Bug in polynomial interpolation?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources