This table summarizes key Excel functions useful for performing multiple regression analysis and diagnostics from Chapter 12.
| Function | Purpose | Example |
|---|---|---|
LINEST(known_ys, known_xs, TRUE, TRUE) |
Returns regression coefficients, standard errors, R², F, etc. | =LINEST(B2:B21, C2:E21, TRUE, TRUE) |
INDEX(array, row_num, column_num) |
Extracts specific value from LINEST output | =INDEX(LINEST(...), 1, 1) for β₁ |
RSQ(known_ys, known_xs) |
Computes R² | =RSQ(B2:B21, C2:C21) |
INTERCEPT(known_ys, known_xs) |
Returns the intercept β₀ | =INTERCEPT(B2:B21, C2:C21) |
SLOPE(known_ys, known_xs) |
Returns slope (β₁ in simple regression) | =SLOPE(B2:B21, C2:C21) |
T.INV.2T(probability, deg_freedom) |
Finds t critical value for CI | =T.INV.2T(0.05, 16) |
DEVSQ(range) |
Total squared deviation (used in SSE or SST) | =DEVSQ(B2:B21) |
STEYX(known_ys, known_xs) |
Estimates standard error of Y given X | =STEYX(B2:B21, C2:C21) |
F.TEST(array1, array2) |
F-test for equal variances (optional diagnostics) | =F.TEST(B2:B21, C2:C21) |
COVARIANCE.P(array1, array2) |
Covariance between X and Y | =COVARIANCE.P(B2:B21, C2:C21) |
CORREL(array1, array2) |
Correlation between variables (check multicollinearity) | =CORREL(C2:C21, D2:D21) |
| VIF = 1 / (1 − R²) | Compute manually to check multicollinearity | Use RSQ() to get R² |
| Cook's Distance (custom) | rᵢ² × hᵢᵢ / [p × (1 − hᵢᵢ)] | Use manually with residuals and leverage |