Forum Discussion

CarloRm2's avatar
CarloRm2
Copper Contributor
Nov 17, 2023

Excel graph with polynomial trendline, equation does not match data

When I create a polynomial trend line for my data graph, which fits the curve of my data well, selecting to display the model equation on the graph, if I try to test the model equation on the data, w...
Formulas and Functions
JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Nov 17, 2023

CarloRm2 

 

First, be sure that you are using an XY Scatter chart, not a Line chart.  The latter uses x={1,2,3,etc}, which might not be the same as your actual Xdata values.

 

If you are copying the coefficients that you see in the trendline label in the chart, you probably need to display them with greater precision.

 

Right-click the trendline and edit the trendline label, setting the number format.  The most general form is Scientific with 14 decimal places.

 

Alternatively, use LINEST in the Excel spreadsheet to calculate the coefficients.

 

For example, for a 3rd-degree polynomial, you would select a horizontal range of 4 cells and enter a formula of the form =LINEST(Ydata, Xdata^{1,2,3}).  The formula must be array-entered (press ctrl+shift+Enter) in versions of Excel before Excel 2016.

 

For more specific directions, attach an example Excel file.

Resources