Trendline functions do not always work correctly

[Damon Bryson]

If you are using the equation from the trendline function, be sure to check
that it is correct. Copy and paste the equation into the spreadsheet, and
regraph the data to ensure the trendline equation matches the trendline on
the chart. I have found that it doesn't always match. If you reformat the
trendline (change to poly or exponential, etc.) and then change it back,
sometimes the equation changes.

 

[Jerry W. Lewis]

Can you give an example of data where the equation changes?

The usual problem when the chart trendline cannot be reproduced is that the
user failed to reformat the equation to dsiplay full precision. That problem
can usually be avoided by using LINEST and/or LOGEST.

Jerry

 

[Damon Bryson]

The following data was graphed and fitted as a 2nd order polynomial. I know
what you mean about the default precision, so I always change the format to
scientific notation with 6 decimal places. In this case, the errors were in
the first and second decimal places. I have not tried using LINEST, because
I thought that only worked on linear fits. The long and sordid details of
the error follow:

1605 -12687
1779 -13818
1886 -14663
1861 -14891
1647 -13650
1344 -11595
832 -7700
660 -6268

The first error identified was a problem displaying the Trendline equation
on the chart. The first term of the equation was dropped off. This appears to
be a problem with the autofitting of the equations label size. When there is
too much text to fit within the label, it drops enough words to fit the
remainder in the box. In this case, the first coefficient of the polynomial
series was dropped. This error was obvious to the user, so it’s not
particularly dangerous. However, it does cause confusion. The workaround is
to reformat the equation box by experimenting with the Trendline options (use
more or less digits, or more
or less polynomial coefficients).

The second error identified was that the label autofitting problem was
copied with the chart into a Word document. When authoring a calculation in
Word, usually charts are generated in Excel and then pasted into Word as a
picture. When this particular chart was copied into Word, even with the
equation displaying properly on Excel, the pasted picture would drop the
first coefficient. This error is a little less obvious, since the user
naturally assumes that a copy/paste operation will paste in the same thing
that was copied.

The third (and most dangerous) error identified was that the equation
displayed on the chart was not correct. The Trendline displayed on the chart
does not correspond to the equation displayed. This can be checked by
programming the displayed equation as an Excel function, and graphing the
result on the same chart. As shown on the attached spreadsheet, the Trendline
does not match the equation resulting from the Trendline function. The
attached spreadsheet shows three different (incorrect) equations that can be
displayed using the same set of data.

The “First Try†was generated by changing the number of polynomial
coefficients from 2 to 3 and back to 2. The resulting equation is
y=0.002047x^2 - 10.371x. The “Second Try†was generated by changing the
Trendline to logarithmic mode, then back to polynomial. The resulting
equation is y=0.001166002x^2 - 10.371x. The “Third Try†was generated by
adding a point to the trended data at
(0,0). This should not make any difference, since the function was already
set to go through (0,0) as a Trendline option. Initially, this did not change
anything, but when the Trendline was changed back to logarithmic mode and
back to polynomial, the function changed to y=0.001357249x^2 - 10.371x. This
is
the closest to the right answer, but still incorrect. Excel 2003 gives the
right answer as y=0.001368719x^2 - 10.371x.

I don't know how to paste an attachment onto this discussion board.

 

[Jerry W. Lewis]

You generally don't past attachments into newsgroup posts--many servers will
filter them out and many readers won't open them.

Prior to 2007, the chart polynomial trendline was numerically excellent.
"Improvements" in 2007 make it untrustworthy (there are several threads on
the subject). It sounds like your problems occurred in 2007, so I suggest
sticking with LINEST.

I agree that Help for LINEST would lead you to expect it to be far less
capable than it is. LINEST will handle up to about 15 x columns for multiple
regression, (does not address whether it can maintain numerical accuracy with
that many columns). The syntax for a quadratic (without having to create a
separate column for the square term) is =LINEST(ydata,xdata^{1,2}). With
your data, coefficients correct to 15 digits are 0.00116719925320468
-9.83852222995009 -311.643831098397. 2003 (and probably 2007) LINEST gives
....91 for the intercept (its least accurate coefficient here). Prior to
2003, LINEST lost an additional 2 figures of accuracy across the board for
this problem.

If you increased the polynomial degree to 6, you would likely be chasing
random noise in this data, as well as posing a very difficult numerical
problem that would greatly reduce the accuracy of estimates.

Note that while the LINEST algorithm in 2003 is greatly improved over
earlier versions, a coefficient of exactly zero (as in 6th degree polynomial
with this data) is not to be trusted unless confirmed by other means. LINEST
2007 may have resolved this problem.

Jerry