Skip to Content

How to Use the LINEST Function in Google Sheets

Google Sheets can perform many useful statistical calculations if you use the right functions.

One such useful statistical function that is available in Google Sheets is the LINEST function. With this function, you can perform both simple linear regression and multiple linear regression.

In this tutorial, I will show you how to use the LINEST function in Google Sheets.

Table of Contents

What is Linear Regression?

Linear regression is a basic predictive analysis used in statistics.

It is used to look at relationships among variables and tries to predict the value of a variable based on the value of another variable.

Regression is commonly used to try and answer two questions:

  1. Are the independent variables useful for predicting the dependent variable?
  2. What independent variables are significant predictors of the dependent variable, and to what magnitude do they affect the dependent variable?

LINEST Syntax

The LINEST function calculates different parameters about the ideal linear trend using the least-squares method when given partial data about a linear trend.

The syntax of the function is:

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

  • known_data_y – an array or range that contains the known dependent values
  • known_data_x – this is an optional argument. It is an array or range that contains the known independent variables. This should be the same length as known_data_y.
  • calculate_b – this is an optional argument that is set to TRUE by default. This controls whether or not to calculate the y-intercept. If TRUE it will calculate the linear form of y-intercept (y=m*x+b). If this is set to FALSE b will be set to zero and it will only calculate the m values.
  • verbose – this is an optional argument that is set to FALSE by default. It controls whether or not to provide additional regression statistics after the slope and intercept.
    • If set to TRUE, this function will include these statistics along with the standard set of linear coefficients for each independent variable and y-intercept:
      • Standard error for each coefficient and intercept
      • The coefficient of determination
      • Standard error for the dependent variables
      • The F statistic or F-observed value that indicates whether the relationship between variables is random instead of linear
      • The degrees of freedom
      • The regression of sum of squares
      • The residual sum of squares

Using LINEST

To use LINEST to only return the regression coefficient and the y-intercept, you only need to enter the first two arguments which are the known Y and known X values.

Linest-1

In the above example, I am looking at data that contains test scores in relationship to hours studied.

The formula used in the example is

=LINEST(B2:B11,A2:A11)

  • B2:B11 – this is the range of data that contains the known_data_y (dependent values)
  • A2:A11 – this is the range of data that contains the known_data_x (independent values)

This formula returns two statistics:

  • 1.63157894736842 – this is the regression coefficient (slope of the line)
  • 76.0263157894737 – this is the y-intercept (the value of y when x is equal to 0)

If you want to return additional statistics with this function you need to set the fourth argument to true.

Here is an example of the function being used to return the additional regression statitics:

Linest-2

In this example my formula is:

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

The formula returns the following statistics:

  • 1.63157894736842 – this is the regression coefficient (slope of the line)
  • 76.0263157894737 – this is the y-intercept (the value of y when x is equal to 0)
  • 1.307829541 – the standard error value for the slope value
  • 5.231318166 – the standard error value for the y-intercept
  • 0.1628623783 – the coefficient of determination
  • 7.210874463 – the standard error for the y estimate
  • 1.556373758 – the F statistic
  • 8 – the degrees of freedom
  • 80.92631579 – regression sum of squares
  • 415.9736842 – the residual sum of squares

Closing Thoughts

Hopefully, by now you have a solid understanding of the LINEST function and how it can be used to quickly provide multiple regression statistics. If you are regularly working with linear regression, then this is a must-know function for you.

It is pretty easy to use once you understand the arguments and the returned values of the function.

More Google Sheets Tutorials:
How to Find Slope