In this article:

May 8, 2024

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.

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

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

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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

We'll email you 1-3 times a week — and never share your information.

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