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