Regression analysis is used to quantify the relationship between one or more explanatory variables and a response variable.
The most common type of regression analysis is simple linear regression, which is used when an explanatory variable and a response variable have a linear relationship.
However, sometimes the relationship between an explanatory variable and a response variable is nonlinear.
In these cases it makes sense to use polynomial regression, which can account for the nonlinear relationship between the variables.
This tutorial explains how to perform polynomial regression in Excel.
Example: Polynomial Regression in Excel
Suppose we have the following dataset in Excel:
Use the following steps to fit a polynomial regression equation to this dataset:
Step 1: Create a scatterplot.
First, we need to create a scatterplot. Go to the Charts group in the Insert tab and click the first chart type in Scatter:
A scatterplot will automatically appear:
Step 2: Add a trendline.
Next, we need to add a trendline to the scatterplot. To do so, click on any of the individual points in the scatterplot. Then, right click and select Add Trendline…
A new window will pop up with the option to specify a trendline. Choose Polynomial and choose the number you’d like to use for Order. We will use 3. Then, check the box near the bottom that says Display Equation on chart.
A trendline with a polynomial regression equation will automatically appear on the scatterplot:
Step 3: Interpret the regression equation.
For this particular example, our fitted polynomial regression equation is:
y = -0.1265x3 + 2.6482x2 – 14.238x + 37.213
This equation can be used to find the expected value for the response variable based on a given value for the explanatory variable. For example, suppose x = 4. The expected value for the response variable, y, would be:
y = -0.1265(4)3 + 2.6482(4)2 – 14.238(4) + 37.213 = 14.5362.
hello Mr. Zach,
I am Antonio Dellisanti from Florence -Italy-
Thanks alot for your “How to Perform Polynomial Regression in Excel” I found very very interesting and I learnt how to use Excel for polynomial regression.
But… how and where do I have to put “the given value” as x= 4 in Excel to find the expected value in this case = 14.5362.?
Thx so much
Sincerely
Antonio
Hi, is there a way to retrieve the coefficients from the trendline function on the chart?
E.g
I want to have the values below in four cells to use in later calculations
-0.1265
2.6482
– 14.238
37.213
Hello. Is the Polynomial Regression with Excel spreadsheet that you describe in the article “How To Perform Polynomial Regressions In Excel” free of charge? I not could you provide me with the price for it?
In Excel 2019, once you have the polynomial plot, equation and r2(squared), how do you find the p-value?
Is it possible to extract the y formula to an excel cell automatically so I don’t have to type it?
Hi Jason…Yes, it is possible to extract the polynomial regression formula into an Excel cell automatically without having to type it manually. Here’s how you can achieve this:
—
### 1. **Enable Excel Add-ins**
Ensure that the Analysis ToolPak add-in is enabled:
1. Go to **File > Options > Add-ins**.
2. In the **Manage** dropdown at the bottom, select **Excel Add-ins** and click **Go**.
3. Check **Analysis ToolPak** and click **OK**.
—
### 2. **Perform Polynomial Regression**
Excel doesn’t directly offer a built-in feature for polynomial regression, but you can achieve it using:
– **Trendline** on a chart.
– A combination of formulas and matrix operations (e.g., `LINEST` function).
#### A. Using Trendline
1. Create a scatter plot:
– Select your data and go to **Insert > Chart > Scatterplot**.
2. Add a trendline:
– Right-click on a data point, choose **Add Trendline**, and select **Polynomial**.
– Choose the order of the polynomial (e.g., 2 for quadratic, 3 for cubic).
3. Display the equation:
– In the **Trendline Options**, check **Display Equation on Chart**.
However, the trendline equation cannot be directly extracted into a cell.
—
#### B. Using the `LINEST` Function
The `LINEST` function can calculate polynomial regression coefficients directly into cells.
**Steps:**
1. **Set Up Your Data:**
Suppose your data is:
– `X`: A1:A10
– `Y`: B1:B10
Create additional columns for higher powers of `X`:
– In column C, calculate `X^2` (e.g., `=A1^2`).
– In column D, calculate `X^3` (if needed for cubic).
2. **Use LINEST for Polynomial Coefficients:**
In an empty range of cells, enter:
“`excel
=LINEST(B1:B10, A1:D10, TRUE, TRUE)
“`
– This outputs the coefficients for the polynomial equation in descending order (e.g., `y = ax^3 + bx^2 + cx + d`).
3. **Extract the Formula into a Cell:**
Using the coefficients returned by `LINEST`, you can manually construct the formula in a cell:
– For example, if the coefficients are `a`, `b`, `c`, and `d` in cells `E1:E4`, you can use:
“`excel
=”y = “&E1&”*x^3 + “&E2&”*x^2 + “&E3&”*x + “&E4
“`
– This dynamically updates if the coefficients change.
—
### 3. **Using VBA to Automate Extraction**
If you need to fully automate the process of extracting the equation, you can use VBA.
**Steps:**
1. Press `Alt + F11` to open the VBA editor.
2. Go to **Insert > Module** and paste the following code:
“`vba
Function PolynomialEquation(XRange As Range, YRange As Range, Order As Integer) As String
Dim Coefficients As Variant
Dim i As Integer
Dim Equation As String
Coefficients = Application.LinEst(YRange, Application.Power(XRange, Evaluate(“ROW(1:” & Order + 1 & “)”) – 1))
Equation = “y = ”
For i = LBound(Coefficients) To UBound(Coefficients)
If i > LBound(Coefficients) Then Equation = Equation & ” + ”
Equation = Equation & Coefficients(i) & “*x^” & UBound(Coefficients) – i
Next i
PolynomialEquation = Equation
End Function
“`
3. Close the editor and return to Excel.
4. Use the custom function:
“`excel
=PolynomialEquation(A1:A10, B1:B10, 2)
“`
Replace `2` with the desired polynomial order.
—
This approach dynamically calculates and outputs the polynomial regression equation into a cell without requiring manual typing. Let me know if you need help setting this up!
I am trying to figure out how to find the Standard Errors on the coefficients of the polynomial trendline created with LINEST(B6:B28,D6:D28^{1,2,3},TRUE, TRUE). With that command and my data, I got the following results
1.4755E-16 -4.90 39.20 1.00
3.4326E-16 0.00 0.00 0.00
1 0.00 #N/A #N/A
1.64875E+31 19.00 #N/A #N/A
107822.9075 0.00 #N/A #N/A
Are the 3.4326E-16, 0.00, 0.00, and 0.00 the standard errors of the variables immediately above?
Hi Billy…Yes, in Excel’s `LINEST` function, when using a polynomial regression model with the syntax `LINEST(y_range, x_range^{1,2,3}, TRUE, TRUE)`, the output matrix consists of multiple rows:
1. **First row**: The estimated coefficients of the polynomial (starting from the highest-degree term down to the intercept).
2. **Second row**: The standard errors of those coefficients.
3. **Third row**: The coefficient of determination (R²), followed by other regression statistics.
4. **Fourth row**: The standard error of the Y estimate (SE_y), degrees of freedom, and other values.
5. **Fifth row**: The regression sum of squares and residual sum of squares.
So in your case:
– The first row contains the polynomial coefficients:
**1.4755E-16 (x³ term), -4.90 (x² term), 39.20 (x term), 1.00 (intercept).**
– The second row contains the **standard errors** of these coefficients:
**3.4326E-16 (SE of x³ term), 0.00 (SE of x² term), 0.00 (SE of x term), 0.00 (SE of intercept).**
It is unusual for standard errors to be exactly zero. This typically indicates:
– Multicollinearity in your data (e.g., if your x-values are highly correlated).
– Data points perfectly fitting the polynomial model.
– A singular matrix issue due to insufficient or collinear data.
You may want to check your input data for multicollinearity or use another tool (such as regression analysis in Python or R) to confirm your results.
Can I try a fourth degree regression analysis curve using this idea?
Hi Miguel…Yes! You can perform a **fourth-degree polynomial regression** in Excel using a similar approach to lower-degree polynomial regression. Here’s how you can do it:
### **Method 1: Using Trendline in Excel Charts**
1. **Enter your data**: Have your independent variable (X) in one column and the dependent variable (Y) in another column.
2. **Insert a Scatter Plot**:
– Select your data.
– Go to **Insert** → **Scatter Plot** (with only markers).
3. **Add a Trendline**:
– Click on any data point in the chart.
– Select **Add Trendline**.
– Choose **Polynomial** and set the **Order** to **4**.
– Check **Display Equation on Chart** to see the regression equation.
– You can also check **Display R² value** to assess the goodness of fit.
### **Method 2: Using Excel Formulas (Manual Regression)**
If you want to calculate the polynomial regression coefficients manually:
1. **Create new columns**:
– Suppose your **X values** are in column **A**, and **Y values** are in column **B**.
– In column **C**, compute \( X^2 \) → `=A2^2`
– In column **D**, compute \( X^3 \) → `=A2^3`
– In column **E**, compute \( X^4 \) → `=A2^4`
2. **Use LINEST for Regression Coefficients**:
– Select **five** adjacent empty cells (e.g., G1 to K1).
– Type the formula:
“`
=LINEST(B2:Bn, A2:E2, TRUE, TRUE)
“`
– Press **Ctrl + Shift + Enter** (for older Excel) or just Enter (for newer versions with dynamic arrays).
– The output will be the coefficients **a, b, c, d, e** for the equation:
\[
Y = aX^4 + bX^3 + cX^2 + dX + e
\]
3. **Predict Y values**:
– In column **F**, use:
“`
=$G$1*A2^4 + $H$1*A2^3 + $I$1*A2^2 + $J$1*A2 + $K$1
“`
– Drag down for all rows.
This method gives you a precise **fourth-degree polynomial regression** without relying on the chart tool.