Charts & Visual Data

Line of Best Fit & Equation Formula: Exact OLS Slope, Intercept & R² via SUMPRODUCT

Extract the exact Scatter-Chart Line of Best Fit equation y = m*x + b and R-Squared directly into cells using closed-form SUMPRODUCT moments — so downstream forecasts reference live trendline coefficients.

Pull chart trendline equations into live cells instead of retyping chart labels!

Checking "Display Equation on Chart" draws static text inside a scatter plot that formulas cannot reference! Using the algebraic covariance identity S_xy = SUMPRODUCT(X, Y) - n * X_bar * Y_bar and S_xx = SUMPRODUCT(X, X) - n * X_bar^2, you obtain exact Ordinary Least Squares (OLS) Slope m = S_xy / S_xx, Intercept b = Y_bar - m * X_bar, and Coefficient of Determination R^2 = S_xy^2 / (S_xx * S_yy) in universal functions!

With m and b stored in live cells, your fitted trendline overlay column (= m * X_i + b) and out-of-sample revenue projections update automatically whenever new observations arrive.

Ad Spend X ($k)	Revenue Y ($k)	OLS Fitted Y_hat = 2.34*X + 24.80	Residual (Y - Y_hat)
10	48	48.20	-0.20
20	72	71.60	+0.40
30	95	95.00 (Centroid)	0.00
40 / 50	118 / 142	118.40 / 141.80	-0.40 / +0.20 (R² = 0.9999)

Exact OLS Best-Fit Slope m via SUMPRODUCT Cross-Moments

Computes S_xy / S_xx across paired ranges A2:A6 (X) and B2:B6 (Y) while guarding against zero X-variance.

=IF(SUMPRODUCT(A2:A6, A2:A6) - COUNT(A2:A6) * AVERAGE(A2:A6) ^ 2 <= 0, "Zero X Variance", ROUND((SUMPRODUCT(A2:A6, B2:B6) - COUNT(A2:A6) * AVERAGE(A2:A6) * AVERAGE(B2:B6)) / (SUMPRODUCT(A2:A6, A2:A6) - COUNT(A2:A6) * AVERAGE(A2:A6) ^ 2), 4))

OLS Y-Intercept b = Mean(Y) - m * Mean(X)

Anchors the best-fit line through the bivariate centroid (X_bar, Y_bar).

=ROUND(AVERAGE(B2:B6) - ((SUMPRODUCT(A2:A6, B2:B6) - COUNT(A2:A6) * AVERAGE(A2:A6) * AVERAGE(B2:B6)) / (SUMPRODUCT(A2:A6, A2:A6) - COUNT(A2:A6) * AVERAGE(A2:A6) ^ 2)) * AVERAGE(A2:A6), 2)

Try the example

Use the original values or change them to test your understanding.