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.