LINEST
The LINEST function returns the parameters of a straight line that passes closest to all available data points using the least squares method.
This function is useful in statistical analysis and forecasting.
Syntax
LINEST(known_data_y, [known_data_x], [b], [stats])
| Argument | Description | Permitted values |
|---|---|---|
| known_data_y | Array or range of known dependent values (y) | Any numerical values, ranges of cells with numbers. Text values or empty cells without numbers are not allowed |
| [known_data_x] | (optional) Values of independent variables that correspond to the “known_data_y” argument. If the argument is not specified, an array of numbers {1;2;3;...} is used. | Any numerical values, ranges of cells with numbers. Text values or empty cells without numbers are not allowed |
| [b] | (optional) The logical value that determines the variable to be calculated in the linear equation y = m·x+b. Calculates the parameter b (TRUE) or the value of the parameter m (FALSE). By default, TRUE | TRUE or FALSE |
| [stats] | (optional) Boolean value that determines whether to calculate additional regression statistics for variables (TRUE) or only the values of variables b and m (FALSE). By default, FALSE | TRUE or FALSE |
Statistics output structure
| Row | Column 1 | Column 2 | Description |
|---|---|---|---|
| 1 | m (Xk) | b | Regression coefficients –m = slope (coefficient at X) –b = free term (constant) Equation: y = m·x + b |
| 2 | SE(m) | SE(b) | Standard errors of coefficients –SE(m) = accuracy of slope estimation –SE(b) = accuracy of constant estimation If SE > 2, then the coefficient is significant |
| 3 | R² | SE(y) | Model quality indicators –R² = coefficient of determination (0 to 1) –SE(y) = standard error of the estimate Y R² = 0.95 means: 95% of the variability in Y is explained by the model |
| 4 | F stat | df | Analysis of variance (ANOVA) –F = F-statistic (model significance) –df = degrees of freedom of residuals = n-k-1 |
| 5 | SSreg | SSres | Sums of squares –SSreg = explained sum of squares –SSres = residual sum of squares |
Examples of use
A simple example of linear regression
If you have sales and expense data and want to find ratios:
=LINEST(A2:A10, B2:B10)
Here, A2:A10 is the range of y values (for example, sales), and B2:B10 is the range of x values (for example, expenses).
Select two cells and press Ctrl+Shift+Enter after entering the formula.
The result will be an array where the first element is the slope coefficient and the second is the free term.
Linear regression calculation with statistics output
Suppose that the y data array is located in cells A1:A7: [1, 3, 5, 7, 10, 15, 19], and the x data array is located in cells B1:B7: [2, 5, 7, 9, 13, 17, 21].
To obtain regression data with statistics, select a cell array of 5 rows × 2 columns, enter the formula =LINEST(A1:A7, B1:B7, 1, 1), and press Ctrl+Shift+Enter.
Result:
| Column 1 | Column 2 | Description |
|---|---|---|
| 0.9601036269 | -1.578238342 | m = slope (coefficient at X) b = free term (constant) Equation: y = 0.9601·x − 1.5782 |
| 0.0336516431 | 0.413713877 | SE(m) = accuracy of slope estimation SE(b) = accuracy of constant estimation Both coefficients are significant, since m/SE(m) = 28.5 > 2 and b/SE(b) = 3.8 > 2. |
| 0.9938949836 | 0.5587736387 | R² = coefficient of determination (0 to 1) SE(y) = standard error of the estimate Y R² = 0.994 means: 99.4% of the variability in Y is explained by the model |
| 813.99862501 | 5 | F = F-statistic (model significance) df = here n (total observations) is specified, not df of residuals F > Fcrit: The model is statistically significant |
| 254.15314582 | 1.5611398964 | SSreg = explained sum of squares SSres = residual sum of squares SSreg/SSres = 162.8: The model explains the data well |
Notes
- The function returns an array of values, so you need to select the range and press
Ctrl+Shift+Enter. - Make sure your ranges are the same length, otherwise the function will return an error.