Math & Engineering
Sum of Squares & Paired Vector Differences (SUMSQ, SUMXMY2, SUMX2MY2 & SUMX2PY2)
Compute vector sum of squares (SUMSQ), squared Euclidean distance (SUMXMY2), difference of squares (SUMX2MY2), and combined squared energy (SUMX2PY2) transparently with SUMPRODUCT.
One unified SUMPRODUCT pattern for all four quadratic vector functions
Regression residual analysis, RMSE forecasting accuracy, and physics kinetic-energy deltas rely on four quadratic vector sums that Excel scatters across cryptic function names: SUMSQ(x) = SUM(x_i^2), SUMXMY2(x, y) = SUM((x_i - y_i)^2), SUMX2MY2(x, y) = SUM(x_i^2 - y_i^2), and SUMX2PY2(x, y) = SUM(x_i^2 + y_i^2).
Writing the algebraic integrand directly inside SUMPRODUCT makes each formula self-documenting to reviewers and unlocks immediate extensions such as Euclidean distance SQRT(SUMPRODUCT((X - Y)^2)) and Root Mean Squared Error SQRT(SUMPRODUCT((Actual - Forecast)^2) / COUNT(Actual)).
| Excel Function | Algebraic Definition | SUMPRODUCT Equivalent | Result on X={3,4,5,12}, Y={1,2,3,10} |
|---|---|---|---|
| SUMSQ(X) | SUM(x_i^2) | SUMPRODUCT(B2:B5 ^ 2) | 9 + 16 + 25 + 144 = 194 |
| SUMXMY2(X, Y) | SUM((x_i - y_i)^2) | SUMPRODUCT((B2:B5 - C2:C5) ^ 2) | 4 + 4 + 4 + 4 = 16 (Dist = 4) |
| SUMX2MY2(X, Y) | SUM(x_i^2 - y_i^2) | SUMPRODUCT((B2:B5 ^ 2) - (C2:C5 ^ 2)) | 194 - 114 = 80 |
| SUMX2PY2(X, Y) | SUM(x_i^2 + y_i^2) | SUMPRODUCT((B2:B5 ^ 2) + (C2:C5 ^ 2)) | 194 + 114 = 308 |
Squared Euclidean Distance (SUMXMY2) & Euclidean Norm
Computes SUM((x_i - y_i)^2) across paired vectors B2:B5 and C2:C5.
=SUMPRODUCT((B2:B5 - C2:C5) ^ 2)Vector Sum of Squares (SUMSQ), Difference of Squares (SUMX2MY2) & Sum (SUMX2PY2)
Evaluates SUM(x_i^2 - y_i^2) across paired coordinates B2:B5 and C2:C5.
=SUMPRODUCT((B2:B5 ^ 2) - (C2:C5 ^ 2))Try the example
Use the original values or change them to test your understanding.