Excel graph with polynomial trendline, equation does not match data

Copper Contributor

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, writing the equation on a column of the sheet, the results do not absolutely correspond to the data column, whereas they do in the case of a linear or logarithmic model. Can someone help me?

1 Reply

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