How to Perform Linear Regression in Google Sheets

The LINEST function in Google Sheets is useful when you need to perform linear regression on two or more variables.

Linear regression allows us to find a trend in the data by attempting to fit a straight line into the available data. 

The rules for using the LINEST function in Google Sheets are as follows:

  • The function can take up to four arguments: known_data_y, known_data_x, calcultte_b, and verbose.
  • The function then outputs the linear coefficients and y-intercept of the linear trend

The linear regression equation is in the form ‘y= a+bx’.  The variable ‘x’ refers to the independent variable, and ‘y’ is the dependent variable. The letter ‘b’ is the slope of the line, and a indicates the y-intercept. 

Let’s begin with a quick use case of using the LINEST function to perform linear regression in Google Sheets.

In this scenario, I am a statistician who needs to predict a certain variable X given a certain value of another variable y. I already have a sample dataset and have transferred it over to Google Sheets. The main output I want is a formula that can help predict the value of X using linear regression.

We can accomplish this with the LINEST function in Google Sheets since we already have our dataset.

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

 

 

The Anatomy of the LINEST Function

The syntax of the LINEST function is as follows:

=LINEST(known_data_y, [known_data_x], [calculate_b], [verbose])

Let’s look at each term in the formula to understand what they mean.

  • = is the equal sign which is how we start any function in Google Sheets.
  • LINEST() is our LINEST function. It computes the linear trend using the least-squares method.
  • known_data_y refers to the array or range containing dependent values already known.
  • known_data_x refers to the values of independent variables that correspond with known_data_y.
  • calculate_b is an optional argument that calculates the y-intercept (b). If FALSE, the function forces b to be 0, which forces the line to pass through the origin.
  • verbose is an optional argument that specifies whether to return more regression properties. By default, it is set to FALSE.
  • If known_data_y is a one-dimensional array, known_data_x may represent multiple independent variables.
  • If the verbose argument is TRUE, LINEST also returns: standard error, coefficient of determination, f-statistic, degrees of freedom, regression sum of squares, and residual sum of squares.

 

 

A Real Example of Performing Linear Regression

Let’s look at a real example of the LINEST function used in a Google Sheets spreadsheet.

In the example below, we have nine data points for which we want to find a trend line for. We were able to get a trendline in a scatter plot chart.

To get the actual equation of this line, we’ll have to perform linear regression on the dataset. Using LINEST, we were able to get the value of the linear equation’s slope and y-intercept. 

example of linear regression in Google Sheets

 

To get the slope and y-intercept, we just need to use the following formula in cell A13:

=LINEST(A2:A10,B2:B10, TRUE, FALSE)

If we set verbose to TRUE, we would see more regression statistics in our result. We have 10 regression statistics computed using the same dataset in the example below.

additional regression statistics

 

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

If you’re ready to try out the LINEST function in Google Sheets, let’s begin writing it ourselves!

 

 

How to Use LINEST Function in Google Sheets

In this section, we will go through each step needed to start performing linear regression in Google Sheets. This guide will show you how we could get the linear equation of the dataset seen previously.

Follow these steps to start using the LINEST function:

  1. First, let’s select the cell that will contain our LINEST function result. In this example, we’ll place our function in cell A13. Since the formula will return two values, cell B13 will also be filled up later.
    select cell to place our LINEST function for linear regression in Google Sheets
  2. Next, we just simply type the equal sign ‘=‘ to begin the function, followed by ‘LINEST(‘. 
  3. You may find a pop-up box with a description of using the LINEST function. We can click on the arrow found on the top-right-hand corner of the box to minimize it.
    enter LINEST function into formula bar

  4. Next, we’ll type in our arguments. We’ll select both ranges x and y. We’ll also set calculate_b to TRUE and verbose to FALSE. Hit Enter on your keyboard to let the function evaluate. In our example, we find out that the line has a slope of 1.997 and intercepts the y-axis at x=3.86.
    linear regression in Google Sheets shows slope and y-intercept

  5. We can also set verbose to TRUE to return more regression statistics, as seen below.
    find linear regression statistics in Google Sheets

 

 

That’s all you need to remember to start using the LINEST function to perform linear regression in Google Sheets. This step-by-step guide shows how simple it is to find the linear equation that fits your dataset.

Linear regression is just one example of a mathematical function you can perform with Google Sheets. With so many other Google Sheets functions out there, you can surely find one that can help you out. 

Are you interested in learning more about how Google Sheets can help you with your tasks?  Stay notified of new Google Sheets guides like this by subscribing to our newsletter!

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