How to Perform Polynomial Regression in Google Sheets

Performing polynomial regression in Google Sheets is useful when you need a more complex model of the relationship between two variables.

Polynomial regression is a type of regression analysis where the relationship between an independent (x) and dependent (y) variable is modeled as an nth-degree polynomial in x.

Let’s look at a scenario where we might need to perform polynomial regression in Google Sheets.

As a statistician, you have a dataset that you might want to use to predict future values. One way to do this is to use regression.

Regression analysis refers to a statistical process that aims to estimate the relationship between a dependent variable and one or more independent variables. 

For example, our dependent variable might be something like the price of a particular lot. Our independent variables may be lot size, location, number of rooms, and so on. We can find a formula or procedure that aims to model the relationship using regression.

The most common regression analysis is linear regression. This involves finding a line that most closely fits the data. In the example below, a linear regression closely follows the data points in our data set. But what if our data does not fit nicely with a linear trend?

Polynomial regressions will allow us to model more complex relationships between variables. These types of regressions are still considered linear since the unknown values x and y display linear properties.

These types of models are usually fitted using the method of least squares. This method minimizes the variance between the actual data and the curve. In Google Sheets, we can use the Chart feature to plot the regression and find the formula of the computed equation.

Later, we’ll look into a particular dataset that might work best with polynomial regression. 

Let’s learn how to perform polynomial regression ourselves in Google Sheets and later test out the function with an actual dataset.

 

 

A Real Example of Polynomial Regression

Let’s look at a real example of polynomial regression in a Google Sheets spreadsheet.

In this example, we have a dataset with two variables, x, and y. Plotting these values into a scatter plot, we come up with the following chart.

polynomial regression in Google Sheets

 

Using linear regression would not fit our data so well if we were to use linear regression. However, with a polynomial regression, we can better model the particular curves found in our sample data.

comparison of linear and polynomial regression in Google Sheets

 

On the left chart, we added a polynomial regression with a polynomial degree of 3. Increasing the degree allows for a more precise equation that works with the current data, leading to overfitting.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to use polynomial regression in Google Sheets, head over to the next section to follow our step-by-step guide!

 

 

How to Perform Polynomial Regression in Google Sheets

This section will go through each step needed to find a polynomial regression in Google Sheets. This guide will show you how easy it is to create a trendline visualization on a scatter plot and return the equation of said trendline.

Follow these steps to start performing polynomial regression:

  1. First, let’s add a chart to start our polynomial regression. The Chart option can be found under the Insert menu.
    Insert chart in Google Sheets
  2. Next, select the Scatter plot option as the Chart type.
    use scatter chart for polynomial regression in Google Sheets
  3. Select the range A1:B11 to select our dataset as the data source. Your scatter plot should look something like this.
    scatter plot of dataset
  4. Under the Customize tab in the Chart editor, check the Trendline option.
    Check the trendline feature
  5. Change the polynomial degree to 3.
    Change polynomial degree
  6. Your scatter plot should now have a trendline that follows a polynomial regression.
    better fit using polynomial regression in Google Sheets
  7. Next, to reveal the equation used to create the line, head back to the Customize tab. Select Use Equation under the Label option.
    Add equation to chart
  8. You should now see the polynomial equation used for the regression. In this example, our equation is 3.8 + 21.8x + 3.73x^2 + 0.174x^3.
    formula of polynomial regression in Google Sheets

 

 

Frequently Asked Questions (FAQ)

  1. When should I consider using polynomial regression?
    Various natural phenomena are known to follow a curvilinear relationship. If a linear regression performs badly with your data, then trying a polynomial regression can help. A polynomial Regression is generally used when a Linear Regression Model does not capture the points in the data. 
  2. How many degrees should I use?
    The number of degrees in a polynomial regression is important because an incorrect degree may lead to underfitting or overfitting the equation. In many cases, domain knowledge would be useful to make this decision.
    For example, trajectory data generally follows a parabolic trend. This would mean that a 2nd order polynomial might make more sense over a 4th or even a 5th order polynomial.

 

That’s all you need to remember to start using the polynomial regression feature in Google Sheets. This step-by-step guide shows how easy it is to use a scatter plot and trendline to visualize and compute a dataset’s polynomial regression.

Polynomial regression is just one example of a statistical procedure we can perform in Google Sheets. With so many other Google Sheets functions out there, you can surely find one that suits your needs.

Are you interested in learning more about what Google Sheets can do? Make sure to subscribe to our Google Sheets newsletter to be the first to know about the latest step-by-step guides and tutorials from us. 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like