Statistical & Clinical Analysis
Calculate & Plot Regression Residuals: Predicted Y, Residual Error & Outlier Flag
Build a linear regression Residual Plot table without Analysis ToolPak: compute Predicted Y_hat = INTERCEPT + SLOPE * X, Signed Residual e_i = Y_i - Y_hat, and Regression Standard Error s_e = SQRT(SSE / (n - 2)).
Why a high R^2 means nothing until a Residual Plot confirms random, homoscedastic scatter around zero!
Anscombe’s Quartet famously demonstrated that curved parabolic data and outlier-skewed datasets can boast an impressive R^2 = 0.67+ while violating linear regression completely! Plotting Signed Residuals e_i = Y_i - Y_hat on the vertical axis against X_i (or Y_hat) on the horizontal axis immediately exposes U-shaped nonlinearity, funnel-shaped heteroscedasticity, and high-residual outliers!
Using dynamic worksheet formulas instead of static Analysis ToolPak output keeps your residual plot live: for each row i across X-range $A$2:$A$6 and Y-range $B$2:$B$6, Predicted value is Y_hat,i = INTERCEPT($B$2:$B$6, $A$2:$A$6) + SLOPE($B$2:$B$6, $A$2:$A$6) * A2, Signed Residual is e_i = B2 - Y_hat,i (whose sum across all n rows equals 0.0000 exactly!), and Regression Standard Error of Estimate with n - 2 degrees of freedom is s_e = SQRT(SUMPRODUCT(e_range^2) / (n - 2))!
| Residual Diagnostic Quantity (n = 5 Calibration Points) | Worksheet Formula | Value & Diagnostic Check |
|---|---|---|
| Predicted Response Y_hat at X_1 = 10 | =INTERCEPT($B$2:$B$6, $A$2:$A$6) + SLOPE($B$2:$B$6, $A$2:$A$6) * A2 | 24.40 |
| Signed Residual e_1 = Y_1 - Y_hat,1 | =B2 - (INTERCEPT($B$2:$B$6, $A$2:$A$6) + SLOPE($B$2:$B$6, $A$2:$A$6) * A2) | +0.60 |
| Sum of Residuals Across All Rows | =ROUND(SUM(C2:C6), 6) | 0.000000 (OLS Orthogonality Check!) |
| Standard Error of Regression s_e (df = n - 2) | =SQRT(SUMPRODUCT(C2:C6, C2:C6) / (COUNT(A2:A6) - 2)) | 1.3038 |
Signed Regression Residual e_1 = Y_1 - (INTERCEPT + SLOPE * X_1)
Computes the signed vertical residual e_1 = B2 - Y_hat,1 for plotting against X_1 on a scatter residual chart.
=IF(OR(COUNT(A2:A6) < 3, VARP(A2:A6) = 0), "Requires >= 3 Points with Distinct X", ROUND(B2 - (INTERCEPT($B$2:$B$6, $A$2:$A$6) + SLOPE($B$2:$B$6, $A$2:$A$6) * A2), 4))Regression Residual Standard Error s_e = SQRT(SSE / (n - 2))
Computes the unbiased OLS residual standard error across helper residual cells C2:C6 using n - 2 degrees of freedom.
=IF(COUNT(A2:A6) < 3, "Requires >= 3 Points", ROUND(SQRT(SUMPRODUCT(C2:C6, C2:C6) / (COUNT(A2:A6) - 2)), 4))Try the example
Use the original values or change them to test your understanding.