Statistical
Ordinary Least Squares Slope, Intercept, R-Squared, and Standard Error (LINEST Diagnostics)
Build labeled, self-documenting regression diagnostics—slope, intercept, R^2, residual standard error SE_y, and slope standard error SE_b—using SLOPE, INTERCEPT, CORREL, and VAR_S.
Unpacking LINEST's unlabeled 5x2 diagnostic grid
LINEST(known_y's, known_x's, TRUE, TRUE) returns a 5-row by 2-column array without row or column labels, forcing reviewers to memorize which cell holds R^2 vs. the standard error of y vs. degrees of freedom.
Computing each statistic in its own labeled cell with SLOPE(Y, X), INTERCEPT(Y, X), POWER(CORREL(Y, X), 2), and residual standard error SE_y = SQRT((1 - R^2) * VAR_S(Y) * (n - 1) / (n - 2)) produces identical OLS diagnostics that any stakeholder can audit at a glance.
| LINEST Statistic | Explicit Formula | Sample Result (n = 5) |
|---|---|---|
| Slope (b1) | =SLOPE(B2:B6, A2:A6) | 1.7 |
| Intercept (b0) | =INTERCEPT(B2:B6, A2:A6) | 1.3 |
| Coefficient of Determination (R^2) | =ROUND(POWER(CORREL(B2:B6, A2:A6), 2), 4) | 0.9897 |
| Residual Standard Error (SE_y) | =ROUND(SQRT((1 - POWER(CORREL(B2:B6, A2:A6), 2)) * VAR_S(B2:B6) * (COUNT(B2:B6) - 1) / (COUNT(B2:B6) - 2)), 4) | 0.3162 |
| Standard Error of Slope (SE_b1) | =ROUND(SE_y / SQRT(VAR_S(A2:A6) * (COUNT(A2:A6) - 1)), 4) | 0.1 |
Residual Standard Error of Regression (SE_y)
Computes SQRT(SS_res / (n - 2)) directly from sample variance VAR_S(Y) and correlation CORREL(Y, X).
=ROUND(SQRT((1 - POWER(CORREL(B2:B6, A2:A6), 2)) * VAR_S(B2:B6) * (COUNT(B2:B6) - 1) / (COUNT(B2:B6) - 2)), 4)Slope, Intercept, R-Squared, and SE of Slope
Dividing SE_y by SQRT(S_xx) yields the exact standard error of the slope coefficient for t-statistic testing (t = b1 / SE_b1 = 17.0).
=SLOPE(B2:B6, A2:A6)Try the example
Use the original values or change them to test your understanding.