In this tutorial, you will learn how to do regression in Google Sheets.
Regression is a statistical method used to investigate the relationship between a dependent variable or variable of interest and one or more independent variables that you think may affect the dependent variable. The most commonly used type of regression is linear regression, which looks for the relationship between the variables by attempting to fit a linear equation, or straight line, to the available data.This is the type of regression covered in this tutorial and is useful for many analysis applications. For example, if your company runs ads each month you might want to look at the relationship between the number of ads run and the monthly sales to see if the ads improve sales. You can use a linear regression to investigate this relationship
The equation for a linear regression line is in the form Y = a + bX where X is the independent variable and Y is the dependent variable. The letter b represents the slope of the line and the a represents the intercept, or the Y value when X =0.
In the example above where the company runs ads to drive sales numbers, the intercept represents the expected sales when no ads are run, while the slope represents the additional sales attributed to each new ad run
Using Scatter Plot to Find Linear Regression Line
An easy way to find the linear regression line in Google Sheets is to chart the data using a scatter plot
Here’s how:
Step 1
Select the data range you want to plot, including headers, then open the Insert menu, and select the Chart option
Step 2
A new chart will be inserted, and the Chart Editor sidebar will appear
To turn this chart into a scatter plot, click on the Chart Type dropdown menu and select the Scatter Chart option:
Step 3
Your chart will update to a scatter plot:
Step 4
Next we’ll add the line of best fit and find the equation. Switch to the Customize tab of the Chart Editor by clicking the word Customize at the top of the editor, then click on the Series section of the menu to expand it:
Step 5
Scroll down in the Series section till you find the checkbox labeled Trendline and check:it
Step 6
A trendline will appear on the scatter plot
Step 7
To find the equation of the trendline, choose Use Equation under Label
Step 8
Checking Show R² will also display the coefficient of determination, which is used to determine the predictive strength of the linear regression model, or how strong the correlation between the independent and dependent variables are. The coefficient of determination ranges from 0 to 1 with higher numbers meaning a stronger correlation. Usually a coefficient of 0.7 is considered good
Summary
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to do regression in Google Sheets. Want more? Check out all the Google Sheets Tutorials.