SUMXMY2 in WPS Spreadsheet: Sum Squared Differences of Paired Values
Calculate squared differences between aligned numeric ranges in WPS Spreadsheet, with a complete dataset, mismatch errors and distinctions from related square-sum functions.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: SUMXMY2 subtracts each pair of corresponding values, squares each difference, then adds the squares. For x=(2,4,7,0) and y=(1,6,3,0), the expected result is 21.
Syntax and the operation being summed
=SUMXMY2(array_x,array_y)
Both arguments are required arrays or numeric ranges. The official formula image defines the sum of (x−y)² over corresponding elements. Position matters: each x is paired with the y in the same position.
This is different from subtracting squared values, x²−y², and from adding squared values, x²+y². Expanding a squared difference introduces the cross term −2xy. Similar function names therefore do not describe interchangeable operations.
Complete worksheet example
Enter the following numbers in A2:B5, with optional headers X and Y in A1:B1. The headers are outside the ranges used in the formula.
| Row | A: X | B: Y | Difference X−Y | Squared difference |
|---|---|---|---|---|
| 2 | 2 | 1 | 1 | 1 |
| 3 | 4 | 6 | −2 | 4 |
| 4 | 7 | 3 | 4 | 16 |
| 5 | 0 | 0 | 0 | 0 |
Enter =SUMXMY2(A2:A5,B2:B5) in D2. The independent expected result is 1+4+16+0=21. The numeric zeros are genuine values and participate in the calculation.
Two useful controls use the same stored dataset:
=SUMXMY2(B2:B5,A2:A5)also gives 21. Reversing every difference changes its sign but not its square.=SUMXMY2(A2:A5,A2:A5)gives 0. Every paired difference is zero.
Keep the row order aligned. Sorting only one of the two columns changes which records are paired and can change the result even though each column contains the same collection of numbers as before.
Advanced use: an error-summary calculation
Suppose X and Y are two aligned sets of measurements in the same unit. The sum of squared differences is 21 in squared units. Dividing by the four complete pairs gives a mean squared difference of 5.25. Its square root is approximately 2.291287847478 in the original unit.
These additional steps are independent arithmetic derived from the worked result. SUMXMY2 itself does not divide by sample size, fit a model or choose a statistical denominator. If residuals come from a fitted model, a variance estimate may require a different denominator; that decision is outside this function’s contract.
For the same data, the sum of x²−y² is 23, while the sum of x²+y² is 115. Neither is the squared-difference result 21. The totals make a practical check against selecting the wrong similarly named function.
Missing values, errors and alignment limits
WPS explicitly states that text, logical values and blank cells in arrays or references are ignored, while zero values are included. It also specifies #N/A when the two arguments have different numbers of elements. Therefore =SUMXMY2(A2:A5,B2:B4) has that documented error expectation.
The short “ignored” statement does not fully explain every mixed-type pairing case. This guide uses complete numeric ranges and does not claim that nonnumeric entries are independently removed and the remaining values re-paired. Clean and align paired records together before interpreting a numerical result.
The source does not establish all error-propagation rules, mismatched two-dimensional shapes, localized array-literal syntax or dynamic array-entry behavior. Using equal-length one-column ranges avoids those unresolved cases. A zero sum means the included numeric pairs have zero differences under this calculation; it does not certify that excluded or missing records were equal.
Evidence and version scope
The official WPS Academy SUMXMY2 lesson, published 2019-06-20, explicitly applies to WPS Office for Windows Personal. It was reviewed on October 18, 2026. The numeric build, introduction release and last supported version remain unknown. The source does not identify this function as deprecated or retired. Support in Linux, other editions and other platforms is not established by this lesson.
The worked examples are original mathematical expectations based on the official contract and formula illustration. They were not executed in WPS. Historical vendor demonstrations are documentation, not our application tests; no screenshot or runtime precision guarantee is supplied. English names and comma separators follow the lesson. Localized entry, workbook interchange and exact output formatting require verification in the intended client.