Non-Linear Regression in Excel

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel Non-Linear Regression

Excel non-linear regression is the widely used model in the statistics field. The dependent variables are modeled as non-linear functions of model variables and one or more independent variables.

We must remember that “linear regression in excel”Linear Regression is a statistical excel tool that is used as a predictive analysis model to examine the relationship between two sets of data. Using this analysis, we can estimate the relationship between dependent and independent variables.read more fits straight linear lines, while non-linear regression creates curves from the data sets.”

Non-Linear-Regression-in-Excel

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Non-Linear Regression in Excel (wallstreetmojo.com)

Examples of Non-Linear Regression in Excel

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

You can download this Non-Linear Regression Excel Template here – Non-Linear Regression Excel Template

Example #1

Let us look at the linear chart first and consider the below data.

Non-Linear Regression Excel Example 1

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 an 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” affects our dependent variable, “Sales.”

For this data, let us create a “Scatter” chart to see these numbers graphically. Then, follow the below steps to insert the excel chart.Insert The Excel Chart.In Excel, a graph or chart lets us visualize information we've gathered from our data. It allows us to visualize data in easy-to-understand pictorial ways. The following components are required to create charts or graphs in Excel: 1 - Numerical Data, 2 - Data Headings, and 3 - Data in Proper Order.read more

Follow the below steps to insert the Excel chart.

  1. First, we must copy and paste the above data to the spreadsheet.

  2. Then, select the data.

  3. Go to the “INSERT” tab and insert a scatter chart.


    Non-Linear Regression Excel Example 1-1

  4. 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.


    Non-Linear Regression Excel Example 1-2

  5. Select the chart to see two new tabs in the ribbon, “Design” and “Format.”


    Non-Linear Regression Excel Example 1-3

  6. Under the “Design” tab, go to “Add Chart Element.”


    Non-Linear Regression Excel Example 1-4

  7. Click on the drop-down list of “Add Chart Element” >> “Trendline” >> “Linear.”


    Non-Linear Regression Excel Example 1-5
    It will add a linear trend line to the chart, and it looks like this.

Non-Linear Regression Excel Example 1-6

From this chart, we can see a clear relationship between “Sales” and “Adds.” As the number of “Adds” increases, the “Sales” numbers invariably increase, which 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.

Non-Linear Regression Excel Example 1-7

If we insert the chart and trend line for this data set, we will get the below kind of chart.

Example 1-8

If we look at our data set’s linear line and dot, there seems to be no exact relationship between the two data points.

These data sets are called Excel “non-linear regression” data points.

Example #2

We will see another example of this Excel non-linear regression data point. Consider the below data.

Example 2

Above is the data on rainfall and crops purchased data.

  • Now, we need to see the relationship between rainfall and crops purchased. For this, we must create a scatter chart.
Example 2-1
  • Insert a linear line for the chart.
Example 2-3

We can see that different crop quantities are purchased for the same rainfall set. For example, look at the rainfall at 20. In this rainfall range, crop purchased quantities are 4598, 3562, and 1184.

It may be due to the season as well. For example, rain could be the same amount, but farmers have purchased different quantities due to different time frames.

Things to Remember

  • Linear and non-linear are two different things from each other.
  • A strong statistical background is required to understand these things.
  • We must understand what linear regression is before learning about non-linear.

Recommended Articles

This article is a guide to Non-Linear Regression in Excel. We discuss how to do non-linear regression in Excel, along with examples and a downloadable Excel template. You may learn more about Excel from the following articles: –