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
LINESTfunction. 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,
LINESTalso 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.
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
- First, let’s select the cell that will contain our
LINESTfunction 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.
- Next, we just simply type the equal sign ‘=‘ to begin the function, followed by ‘LINEST(‘.
- You may find a pop-up box with a description of using the
LINESTfunction. We can click on the arrow found on the top-right-hand corner of the box to minimize it.
- 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.
- 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.
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!