In this article:

How to Use the LINEST Function in Google Sheets (2024 Update)

May 8, 2024

LINEST Function in Google Sheets

The LINEST function in Google Sheets is used for linear regression analysis. This helps find the line of best fit through a set of given data points. This function is particularly useful for statistical analysis involving two variables, where you want to understand the relationship between them, predict future values, or identify trends.

Syntax 

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

  • known_data_y: The set of y-values you already know in the relationship y = mx + b.
  • known_data_x: The set of x-values you already know. If omitted, it assumes x = 1, 2, 3, ... for each y value.
  • calculate_b: A logical value that, if set to TRUE (or omitted), calculates the y-intercept (b) of the line. If set to FALSE, forces the y-intercept to be 0.
  • verbose: A logical value that, if set to TRUE, returns additional regression statistics, such as the standard error of the slope and y-intercept, the coefficient of determination (R^2), and others. The default is FALSE.

Output

The LINEST function can return different types of data based on the verbose parameter:

  • If verbose is FALSE or omitted, it returns only the slope (m) and y-intercept (b) of the line.
  • If verbose is TRUE, it returns an array that includes the slope, y-intercept, and additional regression statistics.

Simple Linear Regression with LINEST Function in Google Sheets 

Simple linear regression is a statistical method used to model the relationship between a single independent variable and a dependent variable by fitting a linear equation to observed data. Follow the steps below to perform simple linear regression with the LINEST Function in Google Sheets. 

1. Prepare Your Data

Organize your data with the independent variable (study hours) in Column A and the dependent variable (test scores) in Column B. Make sure each row corresponds to a data pair.

2. Select Output Range

Select a range that spans multiple cells to accommodate the additional statistics. For simple linear regression with verbose output, select five rows down and two columns wide starting from your desired cell.

linest function google sheets

3. Enter the LINEST Function

With the selected range active, type `=LINEST(B2:B10, A2:A10, TRUE, TRUE)` into the formula bar. Adjust the cell ranges `B2:B10` and `A2:A10` according to your dataset.

4. Confirm with Array Formula

Press `Enter` (or `Ctrl+Shift+Enter` in some versions) to input the formula as an array formula. This action tells Google Sheets you're expecting multiple outputs from the LINEST function.

5. Interpret the Results

The first cell in your selected range shows the slope of the regression line, which predicts the change in test scores for each additional study hour. The second cell in the top row gives the y-intercept, indicating the expected test score when study hours are zero.

Below these, the additional statistics provided include:

  • Standard Error of the Slope and Intercept: These numbers indicate the precision of the slope and intercept estimates. A smaller number suggests a more reliable estimate.
  • R-squared Value: This value, found in the second row of the second column, measures how well the regression line fits the data. A value closer to 1 indicates a better fit.
  • F-statistic and Significance F: These values offer insight into the overall significance of the regression model. They help determine if the observed relationships are statistically significant.

6. Analyze Additional Statistics (Optional)

If you've selected a range that accommodates more output, additional rows will provide further statistical measures, such as the residual degrees of freedom and the sum of squares due to regression, which contribute to understanding the model's accuracy and reliability.

Multiple Linear Regression with LINEST Function in Google Sheets 

Multiple linear regression extends simple linear regression by modeling the relationship between two or more independent variables and a dependent variable that allows for the assessment of the combined effect of multiple factors on an outcome. Follow the steps below to perform multiple linear regression with the LINEST function in Google Sheets. 

1. Organize Your Data

Place your dependent variable (Income in $1000s) in one column (e.g., Column C) and your independent variables (Years of Education, Weekly Study Hours) in adjacent columns (e.g., Columns A and B). Make sure each row corresponds to a data set.

2. Select Output Range

Select a range that can accommodate the regression output including coefficients for each independent variable, the intercept, and additional statistics. For multiple regression with two independent variables and verbose output, select a range that is at least five rows tall and three columns wide.

3. Enter the LINEST Function for Multiple Regression

With the range selected, enter `=LINEST(C2:C10, A2:B10, TRUE, TRUE)` into the formula bar. Adjust `C2:C10` to match the range of your dependent variable and `A2:B10` for your independent variables.

4. Confirm with Array Formula

Press `Enter` (or `Ctrl+Shift+Enter`, if necessary) to enter the formula as an array formula. This ensures that the entire selected range is filled with the regression output.

5. Interpret the Results

The first row in your selected range will show the coefficients (slopes) for each independent variable, indicating the impact of one unit change in the variable on the dependent variable while holding other variables constant. The first cell in the second column shows the intercept, the predicted value of the dependent variable when all independent variables are zero.

The additional statistics provided include:

Standard Error of the Coefficients: These are located directly below the coefficients and indicate their precision. Lower values suggest more reliable estimates.

  • R-squared Value: Found in the second row of the third column, this statistic measures the fit of the regression model. A higher R-squared value indicates a better fit.
  • F-statistic and Significance F: Located further down, these values assess the overall significance of the regression model, indicating whether the observed relationships are statistically significant.

6. Analyze Additional Statistics (Optional)

The selected range will also include further statistical measures if enough space was selected, providing insights into the model’s diagnostics, such as the residual degrees of freedom and the sum of squares due to regression, which help in assessing the model's accuracy and reliability.

We hope that you now have a better understanding of how to use the LINEST function in Google Sheets. If you enjoyed this article, you might also like our article on how to crop images in Google Sheets or our article on Google Sheets spreadsheet ID. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->