How to Do Linear Regression in Google Sheets (2024 Update)

May 8, 2024

Linear regression is a statistical method used to model the relationship between a dependent variable and one or more independent variables by fitting a linear equation to observed data. In Google Sheets, you can perform linear regression analysis to understand how changes in independent variables are associated with changes in a dependent variable.

To perform linear regression in Google Sheets, you primarily use the LINEST function, which provides the slope and intercept of the linear regression line, along with other statistics that can help you understand the strength and direction of the relationship.

The syntax for the LINEST function, which is used to perform linear regression in Google Sheets, is as follows:

LINEST(known_y's, known_x's, [const], [stats])

Here's what each part of the syntax represents:

• known_y's: This is the range of cells that contain the dependent variable (the variable you are trying to predict).

• known_x's: This is the range of cells that contain the independent variable(s) (the variable(s) you are using to predict the dependent variable). For multiple regression, include multiple ranges separated by commas or use a single range that spans multiple columns.

• [const]: This is an optional argument. If TRUE or omitted, the function calculates the y-intercept (b) of the line. If FALSE, the y-intercept is set to zero, and the line will be forced through the origin.

• [stats]: This is another optional argument. If TRUE, the function returns additional regression statistics, such as the standard error of the estimate, R-squared value, etc., in an array. If FALSE or omitted, the function returns only the coefficients (slope and y-intercept) of the regression line.

How to Do Simple Linear Regression in Google Sheets

Simple linear regression is a statistical method used to understand and quantify the relationship between two variables: one independent (X) and one dependent (Y).

Follow the steps below to run a simple linear regression in Google Sheets.

1. Input Data into Google Sheets

Start by inputting your data into Google Sheets. For this example, place your independent variable, the advertising budget (X), in column B, and your dependent variable, monthly sales (Y), in column D. This organization is crucial for the LINEST function to accurately interpret your data. Label the top of these columns as "Advertising Budget" and "Monthly Sales" for clarity.

2. Choose a Cell for Linear Regression Slope Output

Choose a cell where you want the linear regression output to appear, such as E2 for the slope. This selection tells Google Sheets where to display the result of the LINEST function, which calculates the relationship between your advertising budget and monthly sales.

3. Apply LINEST Function to Calculate Slope and Y-Intercept

Type =LINEST(D2:D13, B2:B13) directly into the formula bar, ensuring you replace D2:D13 with the range that matches your monthly sales data and B2:B13 with your advertising budget range.

This formula will calculate the slope of the regression line, which represents the average increase in monthly sales for each thousand dollars spent on advertising. This will also show the y-intercept value which tells you the expected monthly sales when the advertising budget is zero.

4. Interpret Results

Slope: Approximately 3.08

Intercept: Approximately 25.91

These results mean that for every \$1,000 increase in the advertising budget, the monthly sales are expected to increase by about \$3,080. The intercept indicates that if there were no spending on advertising, the monthly sales would still amount to approximately \$25,910.

5. Visualize Relationship with Scatter Plot and Trendline

Highlight both your advertising budget and monthly sales data, then insert a scatter plot through Insert > Chart.

Customize the chart by selecting the scatter plot option. This visual representation helps you see the linear relationship between advertising spend and sales revenue.

How to Do Multiple Linear Regression in Google Sheets\

Multiple linear regression extends simple linear regression to include two or more independent variables (X1, X2, ...) predicting a single dependent variable (Y). This method allows for the examination of the combined effect of several variables on the outcome, providing a more complex and detailed analysis.

Follow the steps below to run multiple linear regression in Google Sheets.

1. Organize Dataset for Multiple Linear Regression Analysis

Input your dataset into Google Sheets with monthly sales (Y) in column D, advertising budget (X1) in column B, and sales calls (X2) in column C. This setup allows you to analyze how both advertising budget and sales calls together affect monthly sales. Label the tops of these columns for easy reference.

2. Select a Range for Multiple Regression Output

Choose a range of cells, like E2:H5, to display the output from the LINEST function. This area needs to be large enough to accommodate the regression coefficients and any additional statistics, like the R-squared value, which measures the fit of your regression model.

3. Calculate Coefficients Using LINEST for Multiple Variables

Type =LINEST(D2:D13, B2:C13, TRUE, TRUE) in the formula bar and press Enter.

This command tells Google Sheets to calculate the coefficients for both your independent variables (advertising budget and sales calls) in relation to monthly sales. It will also provide additional regression statistics because of the TRUE parameters.

4. Analyze Coefficients and Model Fit

Examine the output. The first row shows how much monthly sales are expected to increase with each additional thousand dollars spent on advertising and with each additional sales call. Subsequent rows give you statistical insights, such as the model's overall fit (R-squared value), helping you understand the effectiveness of your sales efforts.

Intercept: Approximately 24.34

Coefficient for Advertising Budget: Approximately 2.01

Coefficient for Sales Calls: Approximately 0.55

In the context of multiple linear regression:

• The intercept of around 24.34 suggests that with no spending on advertising and no sales calls, the monthly sales would be approximately \$24,340.

• The coefficient for the advertising budget of about 2.01 means that, holding sales calls constant, for every \$1,000 increase in the advertising budget, monthly sales are expected to increase by about \$2,010.

• The coefficient for sales calls of about 0.55 indicates that, holding the advertising budget constant, for each additional sales call, monthly sales are expected to increase by approximately \$550.

We hope that you now have a better understanding of how to do linear regression in Google Sheets. If you enjoyed this article, you might also like our article on how to remove all hyperlinks in Google Sheets or our article on why the Google Sheets app keeps crashing.

Get Google Sheets productivity and automation tips delivered straight to your inbox