Trendline Significant Values

[John1791]

I am trying to work with trendlines, and I can match my data with a
polynominal trendline. The plotted trendline matches the data points very
closely, but when I apply the formula generated to actual data, my projected
values are way off. I believe the problem is that the displayed formula is
rounding constants off and not displaying to the number of significant
figures required to generate a workable formula.

Are there any ways to change any settings to allow Excel to display more
figures in the diplayed trendline formulas?

Thank you in advance.

 

[Bernard Liengme]

The results will be very sensitive to how much precision you use for the
coefficients
Ergo: do not use the trendline values but use the results from LINEST
See http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
The use formulas that point to the coefficients in the LINEST results to get
the max precision of Excel
best wishes

 

[BOS]

It looks like there is a problem when the trendline calculates the
coefficient for datasets that are way off from the origin. I found the same
problem. The fitted curve looks great but the coefficients are way off. The
regression algorithm does much better. If the dataset is translated around
the means; i.e., use (x-xaverage) and y-yaverage), the trendline predicted
values also comes out pretty good. The regression algorithm will presumably
differences and perform the regression on the differences, just like the
regression add-in. the fact that the trendline curve is usually pretty good
especially if you go to the sixth order polynomial, means that the regression
is ok, but the reporting of the coefficients when it translates back to the
original dataset is in error. The same results are obtained whether in XP,
2003 or 2007. Microsoft should pay attention!

 

[Jerry W. Lewis]

Please give an example where the trendline in 2003 gives worse results than
LINEST. Prior to 2007, the chart polynomial trendline algorithm was
excelent, and such claims usually involved user error such as using a Line
chart instead of a Scatter chart, or failure to display enough figures for
the chart trendline coefficients.

Jerry

 

[BOS]

Try this dataset:
X Y
0.76 173
0.77 214
0.78 255
0.79 275.5
0.8 296
0.81 306
0.82 316
0.83 326.5
0.84 337
0.85 345
0.86 353
0.87 361
0.88 369
0.89 374.5
0.9 380
0.91 382.5
0.92 385
0.93 387.5
0.94 390
0.95 392.5
0.96 395
0.97 397
0.98 399
0.99 399
1 399

 

[Jerry W. Lewis]

I am not clear about your example; what model did you fit to this data, what
results did you get, and why did you think that LINEST gave you more accurate
results than the chart trendline?

When I fit this data with a polynomial of degree 3 or less in Excel 2003,
LINEST and the chart trendline agree sufficiently that it did not seem worth
determining which was more accurate. In your earlier note, you mentioned a
6th degree polynomial, which I would hesitate to fit to this data since it
has such a narrow range of x -values. Ignoring those misgivings, I found
that for a 6th degree polynomial LINEST gave 0 correct figures for every
coefficient, while the chart trendline gave approximately 10 correct figures
for each of the 7 coefficients.

Jerry

 

[Jerry W. Lewis]

While my conclusion from BOS's example is opposite to what BOS concluded
(i.e. I still maintain that the chart polynomial trendline is superior to
LINEST in Excel versions prior to 2007), the example itself turns out to be
extremely interesting numerically in Excel 2003. I would be very interested
in knowing what Excel 2007 does with it.

MS has documented a patch
http://support.microsoft.com/kb/887964
that seems to fix previous LINEST 2003 errors where coefficients were
incorrectly zero but standard errors were not. It appears that this patch
also excessively tightens a singularity test, so that different coefficients
AND their standard errors are now incorrectly zero.

In Excel 2003, LINEST Without the patch calculates correct coefficients for
the ill-conditioned 6th degree polynomial with data given at
http://groups.google.com/group/microsoft.public.excel/msg/969a2bb33e6cdbb8
With the patch, LINEST zeros the linear coefficient and its standard error.
If I manually construct an orthogonal basis for the 6th degree polynomial,
LINEST without the patch zeros coefficients (but not their standard errors)
for powers 3 through 5.
With the patch, LINEST correctly handles all coefficients and standard
errors from the orthogonal basis, yielding t-tests that agree with those from
summary(lm(y~poly(x,6)) in S-PLUS and R.

Although the present problem is much less ill-conditioned, LINEST 2003 zeros
the cubic coefficient and its standard error with or without the patch.
If I manually construct an orthogonal basis for the 6th degree polynomial,
LINEST without the patch zeros all coefficients except the intercept and
zeros the standard error for the 6th power term.
With the patch, LINEST correctly handles all coefficients and standard
errors from the orthogonal basis, yielding t-tests that agree with those from
summary(lm(y~poly(x,6)) in S-PLUS and R.

In summary, LINEST in 2003 is still not to be trusted when it returns
coefficients of exactly zero. It remains to be seen whether LINEST in 2007
is any better.

Jerry

I am not clear about your example; what model did you fit to this data, what
results did you get, and why did you think that LINEST gave you more accurate
results than the chart trendline?

When I fit this data with a polynomial of degree 3 or less in Excel 2003,
LINEST and the chart trendline agree sufficiently that it did not seem worth
determining which was more accurate. In your earlier note, you mentioned a
6th degree polynomial, which I would hesitate to fit to this data since it
has such a narrow range of x -values. Ignoring those misgivings, I found
that for a 6th degree polynomial LINEST gave 0 correct figures for every
coefficient, while the chart trendline gave approximately 10 correct figures
for each of the 7 coefficients.

Jerry

....