SUMXMY2 in Excel: Sum Squared Paired Differences and Check RMSE
Measure Excel paired squared errors with transparent residuals, alignment controls, RMSE and careful distinctions from differences of squares.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: SUMXMY2 adds (x−y)² for corresponding observations. For x values 3,4,0 and y values 1,2,5, the expected result is 33, from squared differences 4, 4 and 25. The rows must represent the intended pairs.
Availability and version history
- Introduced: first introduction version is not established by the official history reviewed; the current product list is not a launch record.
- Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
- Checked: October 14, 2026; official SUMXMY2 reference. Documentation and independently calculated examples; no native Excel execution or broader platform certification.
Syntax and documented length rule
=SUMXMY2(array_x, array_y)
Both arrays are required. Microsoft documents #N/A if they have different numbers of values. Its referenced-input remarks say text, logical values and empty cells are ignored and numeric zeros are included.
The examples use equal-length numeric columns. The short ignore rule does not settle every mixed-type pairing, array-shape or error-cell case. Do not assume that removing a missing value independently from each column preserves the intended pairs.
Example 1: audit residuals before squaring
Enter A2:B4:
| A · x | B · y |
|---|---|
| 3 | 1 |
| 4 | 2 |
| 0 | 5 |
In C2 enter =A2-B2 and copy down, giving 2,2,-5. In D2 use =C2^2 and copy down, giving 4,4,25.
=SUMXMY2(A2:A4,B2:B4)
=SUM(D2:D4)
Both expected results are 33. The signed residuals total -1, demonstrating how positive and negative errors can cancel before squaring. Keep the residual column when the direction of differences matters.
Example 2: compare with the difference of squares
=SUMX2MY2(A2:A4,B2:B4)
Expected result: -5, not 33. SUMX2MY2 subtracts the arrays’ squared totals, 25−30. It does not measure the squared error of each pair.
For a single pair 3 and 1, the two operations are (3−1)²=4 and 3²−1²=8. The spreadsheet names are similar, but the mathematical parentheses are different.
Advanced example: make row alignment visible
Reorder only the y column to 5,2,1, keeping x at 3,4,0. The differences become -2,2,-1, and the expected SUMXMY2 result changes to 9.
The individual x and y squared totals remain 25 and 30. A check that only sums each column cannot detect that the pairwise meaning changed. Keep a record identifier beside each pair and sort the entire table together.
Neither the smaller result of nine nor the original 33 is intrinsically “better” without knowing which pairing is correct. Data alignment precedes model evaluation.
Advanced example: calculate mean squared error and RMSE
Restore the original three numeric pairs:
=SUMXMY2(A2:A4,B2:B4)/3
=SQRT(SUMXMY2(A2:A4,B2:B4)/3)
Expected values are 11 and approximately 3.316624790. The first is the mean squared difference across exactly three equally weighted pairs; the second is its root mean square, returning to the input unit.
The denominator of three is explicit for this complete numeric dataset. If missingness changes which pairs participate, a raw row count may be wrong. These formulas are also not a blanket recipe for every fitted model’s variance estimator or degrees-of-freedom adjustment.
Advanced example: compare two short prediction lists
In F2:G4 enter observed values 10,12,8 and predictions 9,14,8. Their residuals are 1,-2,0:
=SUMXMY2(F2:F4,G2:G4)
Expected squared-error total: 5. The mean squared error is 5/3, approximately 1.666666667, and RMSE is approximately 1.290994449.
A zero residual is a valid pair with no error, not a missing observation. Counting all three pairs is intentional in this control.
Symmetry, translation and scale checks
Swapping the original x and y arrays leaves the expected result at 33, because each difference reverses sign before being squared. Adding ten to both arrays also leaves it at 33, since the common shift cancels inside every difference.
Doubling every entry in both arrays gives 132, four times the original total. These properties distinguish squared-error scaling from an ordinary absolute difference.
Comparing an array with itself gives 0 in exact mathematics. That is a useful control, but native cancellation, imported types and numerical precision still depend on the actual input and client.
Errors and troubleshooting
A2:A4 with B2:B3 has different numeric lengths and violates the documented rule, with expected #N/A. Verify lengths, record identities and stored types before masking an error or comparing summary scores.
For the combined magnitude of two arrays rather than their disagreement, see SUMX2PY2. The finite checks here do not certify extreme-size performance or a complete native mixed-input matrix.
References and verification
Inputs are fictional. Decimal answers are independent mathematical expectations rounded to nine places, not captured native results. Extreme magnitudes, complete coercion matrices and cross-version numerical accuracy remain untested.