Math & Statistical
XY Scatter Plot Trendline Formulas: Live Cell SLOPE, INTERCEPT, FORECAST & R²
Extract dynamic XY scatter-plot trendline equations directly into worksheet cells—instead of copying static chart label text—using SLOPE(Y, X), INTERCEPT(Y, X), FORECAST(x_new, Y, X), and CORREL(X, Y)².
Why worksheet SLOPE, FORECAST, and CORREL² beat hand-typing static chart trendline labels!
Displaying an equation on an XY scatter plot shows y = m*x + b and R^2 visually, but downstream pricing or calibration formulas cannot reference a chart text box—and rounded chart labels introduce severe truncation errors! Remember the argument order in Excel and Arte Sheets: SLOPE(known_y’s, known_x’s), INTERCEPT(known_y’s, known_x’s), and FORECAST(x, known_y’s, known_x’s) all place dependent Y BEFORE independent X!
Across calibration pairs X = [10, 20, 30, 40, 50] kW and Y = [24, 41, 58, 79, 95] L/min, exact least-squares slope m = 1.8000 L/(min·kW), goodness-of-fit R^2 = (CORREL(A2:A6, B2:B6))^2 = 0.9984, and predicted flow at E2 = 60 kW is FORECAST(60, B2:B6, A2:A6) = 113.40 L/min!
| XY Scatter Regression Statistic (X = A2:A6, Y = B2:B6) | Worksheet Formula (Note Y Before X!) | Evaluated Value |
|---|---|---|
| Trendline Slope m | =ROUND(SLOPE(B2:B6, A2:A6), 4) | 1.8000 L/(min·kW) |
| Goodness of Fit R² | =ROUND((CORREL(A2:A6, B2:B6)) ^ 2, 4) | 0.9984 (99.84% linear fit) |
| Predicted Y at E2 = 60 kW | =ROUND(FORECAST(E2, B2:B6, A2:A6), 2) | 113.40 L/min |
OLS Scatter Trendline Slope m = SLOPE(Known_Ys, Known_Xs)
Guards against constant-X zero variance (STDEV(A2:A6) = 0) before evaluating SLOPE(B2:B6, A2:A6).
=IF(STDEV(A2:A6) = 0, "X Values Cannot All Be Identical", ROUND(SLOPE(B2:B6, A2:A6), 4))Trendline Point Prediction = FORECAST(Target_X, Known_Ys, Known_Xs) & R² = CORREL(X, Y)²
Predicts Y at E2 = 60 along the exact least-squares trendline.
=IF(STDEV(A2:A6) = 0, "X Values Cannot All Be Identical", ROUND(FORECAST(E2, B2:B6, A2:A6), 2))Try the example
Use the original values or change them to test your understanding.