Trendline Significant Values

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.

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"John1791" wrote in message
news
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.

Hi,

What Bernard says is true, and is the best way to proceed, as the
coefficients are placed in cells, but you can easily get Excel to display
more figures for the equation on the chart simply by clicking on the
equation and then increasing the number of decimals using the "Increase
Decimal" toolbar button, or right-click the equation, and Format it to give
more places.

Dave

url:http://www.ureader.com/msg/10296433.aspx

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!

"John1791" wrote:

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.

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" wrote:

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!

"John1791" wrote:

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.

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" wrote:

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" wrote:

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!

"John1791" wrote:

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.

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

"BOS" wrote:

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" wrote:

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" wrote:

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!

"John1791" wrote:

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.

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/micro...9a2bb33e6cdbb8
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

"Jerry W. Lewis" wrote:

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

"BOS" wrote:

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" wrote:

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" wrote:

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