A polynomial trendline is a type of trendline that can capture a non-linear relationship between two variables.
The following step-by-step example shows how to calculate and interpret a polynomial trendline for a dataset in Excel.
Step 1: Create the Data
First, let’s create the following dataset in Excel:
Step 2: Insert Scatter Plot
Next, we will insert a scatter plot to visualize the relationship between the two variables in our dataset.
To do so, highlight the cell range A2:B15, then click the Insert tab along the top ribbon, then click the Scatter icon within the Charts group:
The following scatter plot will automatically appear:
Just from looking at the plot we can tell that the two variables in our dataset do not have a linear relationship.
Thus, a polynomial trendline would likely do a better job of capturing the relationship between the two variables.
Step 3: Insert Polynomial Trendline
To insert a polynomial trendline into our plot, click anywhere on the chart to make it active.
Then click the tiny green plus sign in the top right corner, then click the dropdown arrow next to Trendline, then click More Options:
In the Format Trendline panel that appears, click Polynomial under Trendline Options, then type 3 for the Order.
Lastly, check the box next to Display Equation on chart:
A polynomial trendline with an order of 3 will appear on the scatter plot:
We can see that the trendline does a pretty good job of capturing the relationship between the two variables in our dataset.
We can also see the polynomial trendline equation on the chart:
y = .0904x3 – 1.6059x2 + 8.5934x – 3.3237
We can use the trendline equation to calculate the expected value of y, based on the value of x.
For example, suppose x = 4. The expected value of y would be:
y = .0904(4)3 – 1.6059(4)2 + 8.5934(4) – 3.3237 = 11.1411
When x is equal to 4, the expected value of y is 11.1411.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Perform Polynomial Regression in Excel
How to Perform Quadratic Regression in Excel
How to Add a Quadratic Trendline in Excel
