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])
The LINEST function can return different types of data based on the verbose parameter:
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:
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.
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.
