Nonlinear regression is a regression technique that is used when the relationship between a predictor variable and a response variable does not follow a linear pattern.
The following step-by-step example shows how to perform nonlinear regression in Excel.
Step 1: Create the Data
First, let’s create a dataset to work with:
Step 2: Create a Scatterplot
Next, let’s create a scatterplot to visualize the data.
First, highlight the cells in the range A1:B21. Next, click the Insert tab along the top ribbon, and then click the first plot option under Scatter:
The following scatterplot will appear:
Step 3: Add a Trendline
Next, click anywhere on the scatterplot. Then click the + sign in the top right corner. In the dropdown menu, click the arrow next to Trendline and then click More Options:
In the window that appears to the right, click the button next to Polynomial. Then check the boxes next to Display Equation on chart and Display R-squared value on chart.
This produces the following curve on the scatterplot:
Note that you may need to experiment with the value for the Order of the polynomial until you find the curve that best fits the data.
Step 4: Write the Regression Equation
From the plot we can see that the equation of the regression line is as follows:
y = -0.0048x4 + 0.2259x3 – 3.2132x2 + 15.613x – 6.2654
The R-squared tells us the percentage of the variation in the response variable that can be explained by the predictor variables.
The R-squared for this particular curve is 0.9651. This means that 96.51% of the variation in the response variable can be explained by the predictor variables in the model.
Additional Resources
How to Perform Simple Linear Regression in Excel
How to Perform Multiple Linear Regression in Excel
How to Perform Logarithmic Regression in Excel
How to Perform Exponential Regression in Excel