Trendline Not The Same as Equation Displayed

[Dan]

I plotted data from lab measurements on an Excel chart,
then asked the program to create a trendline for the data
and display the equation for the trendline on the graph.
The only problem is that the equation Excel produced is
completely different than the trendline shown.

The trendline is a 5th order polynomial for the data set:
x
20
25
30
35
40

y
194
85
57
43
31

The equation displayed is:
y = 0.0037x4 - 0.4927x3 + 24.728x2 - 552.38x + 4705

The equation transposed to spreadsheet:
=(0.0037*A2^4)-(0.4927*A2^3)+(24.728*A2^2)-(552.38*A2)+4705

where A2 is the x value. The plot of this equation is not
the same as the trendline given.
Anybody got a clue to this discrepancy?

 

[Jon Peltier]

Dan -

You probably need more significant digits in the trendline. Select the
trendline, press Ctrl-1 (one) to format it, and on the number tab,
select a Scientific format with 14 digits.

- Jon

 

[Jerry W. Lewis]

You don't have enough data to fit anything beyond a 4th order polynomial.

In addition to John's suggestion to display enough figures on the chart
to be meaningful, you could use the LINEST worksheet function. If the x
data are in A2:A6 and the y data are in B2:B6, then the array formula
=LINEST(B2:B6,A2:A6^{1,2,3,4})
would calculate the polynomial coefficients.

In Excel versions prior to 2003, the trendline (with enough displayed
figures) tends to be more accurate than LINEST. For this problem, the
LINEST coefficients are accurate to 8 figures, and the trendline
coefficients are accurate to 12.

Jerry