Excel Non-Linear Regression
Excel Non-Linear Regression is the model which is used widely in the statistics field where the dependent variables are modeled as non-linear functions of model variables and one or more independent variables.
The simple thing we need to remember is “linear regression in excel” fits straight linear line and on the other hand non-linear regression creates curves from the data sets”.
Examples of Non-Linear Regression in Excel
Example #1
Let’s look at the linear chart first consider the below data.
In the above data we have two variables “Sales” and “Adds”.
We need to understand which dependent variable is and which independent variable is.
In general, we all know “Adds” play a vital role in increasing the possibility of revenue generation. So, “Sales” is dependent on “Adds”, this means “Sales” is a dependent variable and “Adds” is Independent variable.
The general rule is one of the variables is going to affect the other one. So, in this case, our independent variable “Adds” affecting our dependent variable “Sales”.
For this data lets create a “Scatter” chart to see these numbers graphically. Follow the below steps to insert the excel chart.
Step 1: Copy and paste the above data to the spreadsheet.
Step 2: Select the data.
Step 3: Go to the INSERT tab and insert a Scatter chart.
Now we will have a chart like this. For this chart, we need to insert a linear line to see how linear these data points are.
Step 4: Select the chart see two new tabs in the ribbon, “Design” and “Format”.
Step 5: Under the “Design” tab go to “Add Chart Element”.
Step 6: Click on the drop-down list of “Add Chart Element” >> Trend line >> Linear.
This will add a linear trend line to the chart and it looks like this.
From this chart, we can see a clear relationship between “Sales” and “Ads”. As the number of “Ads” increases it invariably increases the “Sales” numbers as well and it is proved right by our linear line in the chart. It just fits on the linear line.
Now, look at the example data of the same thing.
If you insert the chart and trend line for this data set we will get the below kind of chart.
If you look at the linear line and dot of our data set, it seems to be a not exact relationship at all between the two sets of data points.
These kinds of data sets are called excel “Non-Linear Regression” data points.
Example #2
Now we will see another example of this excel non-linear regression data point. Consider the below data.
Above is the data of Rain Fall and Crops Purchased data.
- Now we need to see the relationship between rainfall and crops purchased. For this create a scattered chart.
- Insert a linear line for the chart.
As we can for the same set of rainfall different crop quantities purchased. For example look at the rainfall at 20, in this rainfall range crop purchased quantities are 4598, 3562, and 1184.
This may be due to the season as well. Rain could be the same amount but due to different time frame farers have purchased different quantities.
Things to Remember
- Linear and Non-Linear are two different things from each other.
- A strong statistical background is required to understand these things.
- Understand what linear regression is before learned about non-linear.
Recommended Articles
This has been a guide to Non-Linear Regression in Excel. Here we discuss how to do non-linear regression in excel along with examples and downloadable excel template. You may learn more about excel from the following articles –
- Interpolation in Excel
- How to do Linear Interpolation in Excel?
- How to do Regression Analysis in Excel?
- What is Analysis ToolPak in Excel?
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
