Linear Regression In Google Sheets

What Is Linear Regression In Google Sheets?

Linear regression in Google Sheets is a data analysis method that forecasts the value of a response variable according to the explanatory variable value. The method works based on the Least Squares technique.

Users can utilize the linear regression technique in Google Sheets to determine the variations in a firm’s stock price, considering the firm’s quarterly revenues.

For example, the source dataset contains the count of tickets closed data in different hourly durations.

The requirement is to create linear regression in Google Sheets to understand how the count of tickets closed varies with the hours spent.

Then, we can calculate linear regression in Google Sheets using the LINEST(), which works similar to the Excel LINEST function. Assume the target cell range is E2:F6.

In this simple linear regression in Google Sheets example, we choose the first target cell E2 and enter the LINEST().

The LINEST() accepts four arguments as its input. While the first one is the known dependent variable data range B2:B11, the second is the known independent variable data range A2:A11. Next, the third argument value is the logical TRUE to determine the y-intercept. Finally, the fourth argument value is the logical TRUE to secure the additional regression statistics.

Thus, once we press Enter, the function executes as an array formula, similar to Excel array formulas, returns the required regression statistics in cells E2:F6.

We shall see the relevant values’ interpretation:

The R Square value is 0.87794 (Coefficient of Determination). It is the ratio of the variance in the response variable that the explanatory variable can define. So, around 87.79% of the variation in the tickets closed can be accounted for by the number of hours spent.

The standard error value is 6.0253. It indicates the mean distance that the observed data points are from the regression line. Thus, the observed data points are on an average of 6.0253 units away from the regression line.

Next, the coefficients provide us with the values required to achieve the predicted linear regression equation. So, the required linear regression equation is the following:

Tickets Closed = 11.3913*(hours) + 13.0434

The equation indicated that for every hour spent, the count of tickets closed is estimated to increase by 11.3913, on average. Next, for the intercept to average that the predicted tickets closed count in zero hours is 13.0434.

Now, we can utilize the predicted regression equation to evaluate the estimated count of tickets closed based on the count of hours spent. For instance, the tickets closed in 6 hours is 81.3912:

Tickets Closed = 11.3913*(6) + 13.0434 = 81.3912

How To Do Linear Regression In Google Sheets?

Before we see the steps to create linear regression in Google Sheets, we must know the LINEST() syntax and the different techniques to apply it in Google Sheets.

The LINEST() in Google Sheets syntax is the following:

Where,

• known_data_y: The existing data points for the dependent or response variable (y).
• known_data_x: The existing data points of the independent or explanatory variable (x).
• calculate_b: The value represents if the y-intercept (b) must be evaluated. The argument’s default value is the logical TRUE.
• verbose: The value denotes if we want to get the additional regression statistics or only the coefficients for slope and intercept values. The argument’s default value is FALSE. When the argument value is the logical TRUE, the function output is all the additional regression stats, along with the slope and intercept values. On the other hand, when we supply the argument value as FALSE or leave it blank, the function returns only coefficients for the slope and intercept statistical figures.

While the first argument in the LINEST() is mandatory, the remaining three arguments are optional.

Furthermore, we can apply the LINEST() in Google Sheets in the following ways:

1. Access the function from the ribbon.
2. Enter the function into the sheet manually.

Method #1 – Access The Function From The Ribbon

Choose a target cell for showing the result → The Insert tab → The Function option right arrow → The Array function group right arrow → The LINEST function.

The chosen function gets inserted in the target cell, with the cursor inside the function brackets. We can now update the function arguments within the brackets.

Further, click the ‘?’ symbol on the left of the function name to view the function syntax.

We can then click the down arrow in the syntax pane to understand the meaning of the LINEST() In Google Sheets explained with the help of a basic illustration.

Finally, press Enter to acquire the function output.

Method #2 – Enter The Function Into The Sheet Manually

Now, we can calculate linear regression in Google Sheets using the following steps:

1. Update the Google Sheets file with the known response and explanatory variables’ data.
2. Choose a target cell and enter the LINEST() with the required argument values.
3. Press Enter to view all the regression statistics in the consecutive and adjacent cells to the target cell.
4. Finally, label each statistical figure to make the output more understandable. Now, we can interpret the linear regression statistics to review the relationship between the response and explanatory variables.

Furthermore, the steps to plot the linear regression graph to analyze the relationship between the response and explanatory variables visually are as follows:

1. Choose the range containing the known response and explanatory variables’ values, including the column titles.
2. Choose the Insert tab à The Chart option.
3. The Chart editor pane opens, which has two tabs, Setup and Customize.
4. Click the Chart type field dropdown button in the Setup tab to choose the Scatter chart, which is similar to Excel Scatter plot, as the required plot type. Next, the chosen range will appear in the Data range field.
5. Next, click the Customize tab to open it. Click the Series section, where we must select the Trendline option. Once we do that, we will see a few more options, where we must click the Label field dropdown button to set the field as Use Equation. Next, check the Show R² check box.

Now, we can use the linear regression equation and the R² value to perform the regression analysis.

Examples

The following examples show the practical applications of linear regression analysisin Google Sheets.

Example #1 – Using Formulas

We shall see an example of simple linear regression in Google Sheets using inbuilt formulas.

The dataset contains advertising budgets and the monthly sales they generate.

If we want to find out how the monthly sales figures vary with the advertising budgets, we can use linear regression statistics to perform the analysis.

Typically, a simple linear regression, similar to the Excel Linear Regression, involves one dependent and one independent variable, with the following being the linear regression equation:

y = bx + a

Where,

• y: Dependent variable
• x: Independent variable
• b: Slope
• a: Intercept

In this example, the Monthly Sales is the y variable, and the Ads Budget is the x variable.

Thus, if we find the slope and intercept values, we can form the linear regression equation to predict Monthly Sales values based on the Ads Budget values. So, we can use the SLOPE() and INTERCEPT(), which work as the Excel SLOPE function and INTERCEPT function, to determine the two parameters using the historical Monthly Sales and Ads Budget values.

We shall use the columns E and F cells to display the output values.

• Step 1: Select cell E3, enter the SLOPE(), and press Enter.

=SLOPE(B2:B11,A2:A11)

• Step 2: Select cell F3, enter the INTERCEPT(), and press Enter.

=INTERCEPT(B2:B11,A2:A11)

Thus, the required simple linear regression expression to estimate the Monthly Sales values (y-values) based on the Ads Budget values (x-values) is as follows:

Monthly Sales ($) = 9.005*(Ads Budget) + 15.447

However, we might need additional linear regression statistical figures to do a detailed analysis. For that, we can use the LINEST().

• Step 3: Select cell E6, enter the LINEST(), and press Enter.

=LINEST(B2:B11,A2:A11,TRUE,TRUE)

[ Alternatively, choose the target cell and then Insert → Function → Array → LINEST.

The chosen function will appear in the target cell.

Next, enter the argument values inside the bracket.

• Step 4: Press Enter to view the LINEST() output.

The function output includes other critical linear regression statistics, along with the slope and intercept coefficients, which we can interpret as explained below:

The R Square value is 0.930 (Coefficient of Determination). It is the ratio of the variance in the response variable that the explanatory variable can define. So, around 93% of the variation in the Monthly Sales can be accounted for by the Ads Budget.

The standard error value is 8.2953. It indicates the mean distance units that the observed values are from the regression line.

Next, the coefficients provide us with the values required to get the predicted linear regression equation, which is the following:

Monthly Sales ($) = 9.005*(Ads Budget) + 15.447

The equation shows that for the allotted ad budget, the monthly sales are estimated to rise by 9.005, on average. Next, for the intercept to average that, the predicted monthly sales in the case of zero ads budget is 15.447.

Example #2 – Linear Regression Graph

The source dataset contains the yearly stock units purchased data.

We shall see the steps to prepare a linear regression plot for the given data.

• Step 1: Choose the range A1:B11 and then Insert → Chart.

• Step 2: The Chart editor pane will appear, where we must set the Chart type field as Scatter chart in the Setup tab.

Further, the second field, Data range, will show the chosen cell range.

• Step 3: Click the Customized tab to open it.

Next, click the Chart & axis titles section to update the chart and axis titles utilizing the first two fields.

Please use the other fields in the section to get the titles according to the requirements.

We can use the first field’s dropdown button to choose the required axis and the second field to enter the text we want to view as the respective axis title.

• Step 4: Click the Series section and scroll down.

Check the Trendline checkbox.

Next, scroll down to set the Label field as Use Equation, using the field’s dropdown button and list.

Next, check the Show R² checkbox.

Finally, close the Chart editor pane.

Now, we have the linear regression plot, showing the trendline, the linear regression equation and the R² value required for the regression analysis.

Example #3

Let us understand the concept of multiple linear regression in Google Sheets with an example.

The source dataset contains vehicle ages, annual maintenance costs, and vehicle cost data.

In this case, the dependent variable is the Vehicle Cost, and it depends on two independent variables: The Vehicle Age and Yearly Maintenance Cost.

So, we must perform multiple linear regression in Google Sheets, and here is how to achieve the required statistical values for the analysis using the LINEST(). Assume the target cell range is F2:H6.

• Step 1: Select cell F2, enter the LINEST(), and press Enter.

=LINEST(C2:C11,A2:B11,TRUE,TRUE)

While we can use all the statistical values, we can ignore the cells containing the #N/A error, as those values are irrelevant to the analysis.

The linear regression analysis is as follows:

• Intercept: Estimated as 9584.303
• Coefficient for Vehicle Age: Estimated as -810.55
• Coefficient for Yearly Maintenance Cost: Estimated as 2.647

The intercept value of 9584.303 indicates that with no annual vehicle maintenance cost and not considering the vehicle age factor, the vehicle cost would be around $9584.303.

‍The coefficient for the Vehicle Age of around -810.55 indicates that holding the annual vehicle maintenance cost constant, for each year reduced from the vehicle age, the vehicle cost is expected to increase by $810.55.

‍The coefficient for Yearly Maintenance Cost of around 2.647 denotes that, with the vehicle age constant, for each additional dollar of annual maintenance cost added, the vehicle cost is expected to rise by approximately $2.647.

Important Things To Note

• Ensure we are clear about the response and explanatory variables before proceeding with the linear regression in Google Sheets. Otherwise, the regression statistics will be incorrect.
• Ensure the dependent and independent variables’ data range lengths supplied to the LINEST() in Google Sheets are the same. Otherwise, the function output will be the #REF! error.
• Ensure the last argument, verbose, value supplied to the LINEST() in Google Sheets is TRUE. Otherwise, the function returns only the coefficients for the slope and intercept.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Linear Regression In Google Sheets, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Linear Regression In Google Sheets. We explain how to use the LINEST() in Google Sheets with examples & points to remember. You can learn more from the following articles –

• Exponential Function in Google Sheets
• MODE Google Sheets Function
• Data Validation In Google Sheets