Trendline Coefficients Differ from Linest

**jzmuda** · 05-27-2009, 08:57 PM

When I plot the following data in a xy chart:, the trendline coefficients differ significantly from the results yielded using the linest polynomial equation.

1.692231697901180E+00 1.007320E-01
1.751487512094260E+00 6.969200E-02
1.801485053879390E+00 4.857330E-02
1.860740868072470E+00 2.943180E-02
1.919996682265560E+00 1.891620E-02
1.977400841977050E+00 1.252140E-02
2.032953347206950E+00 8.599360E-03
2.088505852436850E+00 6.218410E-03
2.144058357666750E+00 4.334050E-03
2.205165826341420E+00 3.180580E-03

The 6th order polynomial coefficients (from a6 down to a0) resulting from LINEST are:

3.22981349367398 -28.27012235351990 91.13758396527950 -116.75964325150500 0.00000000000000 126.13519446718700 -78.44804778602400

The 6th order polynomial coefficients (from a6 down to a0) resulting from the trendline are:
-2.465924391528820E+01 2.979221668355000E+02 -1.495759455513340E+03 3.993457659476170E+03 -5.977809562228490E+03 4.754781875397230E+03 -1.569142463507590E+03

Both fits produce good correlation with the original data. If I change the order of the fit to 5th, then the results from linest and the trendline are the same. Also, if I change the data set to the following, the results between the two are the same (for 5th or 6th order).

9.333616979011810E-01 1.007320E-01
1.013258512094260E+00 6.969200E-02
1.080672053879390E+00 4.857330E-02
1.160568868072470E+00 2.943180E-02
1.240465682265560E+00 1.891620E-02
1.317865841977050E+00 1.252140E-02
1.392769347206950E+00 8.599360E-03
1.467672852436850E+00 6.218410E-03
1.542576357666750E+00 4.334050E-03
1.624969826341420E+00 3.180580E-03

Any ideas? This is driving me crazy!

Thanks,

Jay

PS This question was also posted at http://help.lockergnome.com/office/m...124742#3124742 but the answer was determined here first, so don't bother going to the other post.

**shg** · 05-27-2009, 09:39 PM

Welcome to the forum.

Post the workbook.

**jzmuda** · 05-28-2009, 02:32 PM

I have attached a workbook wich contains the data that is giving me trouble. Hope this helps in the diagnosis process.

Best,

Jay

**shg** · 05-28-2009, 03:44 PM

LINEST and the chart trendline use different methods to arrive at the coefficients (don't ask me why), but they both yield good fits. So do these, which I calculated after normalizing x to have a maximum value of 1:

Code:

       -A-- ----B-----
   3   Term   Coeff   
   4   x^6     2.44489
   5   x^5   -19.06111
   6   x^4    46.19764
   7   x^3     0.00000
   8   x^2  -170.33818
   9   x^1   258.43576
  10    a   -121.18739

(When x values are large, you have to do this, or numerical precision eats you alive.)

So pick any one you like.

However, you're using 7 numbers (the coefficients) to describe 10 (the data). You can fit a polynomial perfectly to any set of (single-valued) points if the number of points is the same as the number of coefficients. Your data can be almost equally well described using two numbers (an exponential trendline); R2 = 9.967.

Is there a particular reason to use a polynomial?

**jzmuda** · 05-28-2009, 05:23 PM

Ok, at least now I understand why the results from LINEST differ from those obtained using trendline. Now that I know that, I am comfortable using either set of coefficients, I just wasn’t comfortable when I couldn’t see why there was a discrepancy.

Although the data I presented has only 10 data points over the range, I need to solve for many more intermediate points. The data points I presented are pulled from a graph. I only plotted enough to do a good job representing the curve shape. The curve fit equations allow automated enquiry of the data contained in the graph.

As for why I am using the polynomial; simply because the results are more accurate. The exponential is close, but for my purposes, it is good to be as close to the original data as possible.

Thanks very much for your help,
Jay