Trendline problem

**sandeep.gupta** · 06-02-2011, 04:56 PM

hi,

I have a plot (non linear) and I added a trendline (5th polynomial). The trendline looks good with a R2 value of .9995. I also displayed the equation of trendline. Now, when I take the equation of trendline and plug back the the X value (same X value which was used to plot the graph) then it gives me very different value of Y.

Any help please?

Thanks

**shg** · 06-02-2011, 05:17 PM

You can't get the coefficients with sufficient precision from the trendline. Use LINEST to get the coefficients on the worksheet. If you need help, post a workbook.

**sandeep.gupta** · 06-02-2011, 05:23 PM

Originally Posted by shg

You can't get the coefficients with sufficient precision from the trendline. Use LINEST to get the coefficients on the worksheet. If you need help, post a workbook.

How do i attach a file with my post so that you can look it up?

**davegugg** · 06-02-2011, 05:18 PM

Can you provide the equation of the trendline and the expected data point?
Note that not all points fit a trendline, and some can vary wildly - your graph should show you which points don't fit the trendline very well.

**sandeep.gupta** · 06-02-2011, 05:26 PM

Originally Posted by davegugg

Can you provide the equation of the trendline and the expected data point?
Note that not all points fit a trendline, and some can vary wildly - your graph should show you which points don't fit the trendline very well.

Here are the data points
X axis Y-Axis
35.5 33946
39.1 27985
46.6 20009
55.8 14012
65.4 10036
71.7 8084

When i fit a 5th order polynomial it gives me this equation

y = 32.608x5 - 653.79x4 + 5084x3 - 18102x2 + 21552x + 26033

I actually tried with 4th, 3rd, 2nd order polynomial but the values are wayyyy different.

**shg** · 06-02-2011, 06:39 PM

How do i attach a file with my post so that you can look it up?

It's explained in those forum rules I asked you to read.

With a polynomial trendline, you can fit anything.

Seems like your data is more simply described by a power series; two parameters, R2 = 0.9992

**sandeep.gupta** · 06-03-2011, 09:23 AM

Originally Posted by shg

How do i attach a file with my post so that you can look it up?

It's explained in those forum rules I asked you to read.

With a polynomial trendline, you can fit anything.

Seems like your data is more simply described by a power series; two parameters, R2 = 0.9992

With power series it gives me R2 value as .93. Its a bad fit to the curve.

**davegugg** · 06-03-2011, 10:41 AM

Where'd you get that polynomial? When I put it in, I get very different y values based on your x values.

This is what a polynomial regression tool gave me, and it works much better:
y = -7.038024138·10-4 x5 + 2.026496283·10-1 x4 - 23.41763479 x3 + 1369.214272 x2 - 41301.80638 x + 540114.1393