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.