Statistical
R-Squared (R^2) and linear regression residuals formula
Measure how much variance an OLS trendline explains with CORREL(X, Y)^2 and compute fitted values and residuals Y - Y_hat.
What R-Squared = CORREL(X, Y)^2 actually tells you
For simple OLS with an intercept, R-squared equals CORREL(X,Y)^2 for the same numeric pairs.
In the example, 0.9941 describes the fraction of in-sample variation accounted for by the fitted line.
| AdSpend $k (A) | Sales $k (B) | Fitted Y_hat = 9.5 + 2.6*X | Residual (Y - Y_hat) |
|---|---|---|---|
| 1 | 12 | 12.1 | -0.1 |
| 2 | 15 | 14.7 | +0.3 |
| 3 | 17 | 17.3 | -0.3 |
| 4 | 20 | 19.9 | +0.1 (Sum = 0.0) |
R-Squared, fitted prediction, and residual formulas
The formula displays R-squared rounded to four decimals.
=ROUND(CORREL(A2:A5, B2:B5) ^ 2, 4)Row residual (Actual Y minus OLS Predicted Y_hat)
A positive residual means observed Y is above the fitted value; a negative residual means it is below.
=ROUND(B4 - (INTERCEPT(B2:B5, A2:A5) + SLOPE(B2:B5, A2:A5) * A4), 2)Inputs and interpretation
CORREL squared equals R-squared for simple ordinary least-squares regression with an intercept, using the same numeric pairs and nonconstant X and Y. This is an in-sample fit measure, not a guarantee of future prediction or causation. Residuals sum approximately to zero in that fitted intercept model; a positive residual merely means observed Y exceeds fitted Y.
Try the example
Use the original values or change them to test your understanding.