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

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.

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

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.

5. We can also set verbose to TRUE to return more regression statistics, as seen below.

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.

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.

## How to Extract Data from Excel or CSV file to Google Sheets

This guide will teach you how to extract data from Excel or CSV file to Google Sheets. In…

## How to Apply Conditional Formatting Based on Another Cell in Google Sheets

Learning how to apply conditional formatting based on another cell in Google Sheets is useful to analyze a…

## How to Use BYROW Function in Google Sheets

This guide will explain how to use BYROWfunction in Google Sheets. When we need to group an array…

## How to Calculate Percent Change in Google Sheets

You can write a simple formula to calculate percent change in Google Sheets. It’s essential to be aware…