SUMX2MY2
Returns the sum of the difference of squares of corresponding values in two arrays: Σ(x² − y²). Used in regression analysis.
SUMX2MY2(array_x, array_y)Arguments
array_xThe first array or range of values.array_yThe second array or range of values (must match the size of array_x).
=SUMX2MY2({2,3,4},{1,2,3})(4-1) + (9-4) + (16-9) = 9
=SUMX2MY2(A1:A3,B1:B3)Σ(Aᵢ² − Bᵢ²)
- •Returns #N/A if the two arrays have different sizes
- •Empty cells, logical values and text are ignored
- •Compare with SUMX2PY2 (sum of sums) and SUMXMY2 (sum of squared differences)
- •Mismatching the size of array_x and array_y, which returns a #N/A error since every value in one array needs a corresponding value in the other
- •Confusing SUMX2MY2 (sum of x²−y², a single running total of differences) with SUMXMY2 (sum of squared differences, (x−y)² for each pair) - despite the similar names, the order of subtracting and squaring is completely different
- •Assuming the result must be positive - since it's a running sum of x²−y² across pairs, the total can easily be negative if the y values are generally larger than the corresponding x values
SUMX2PY2Calculates the sum of x²+y² instead of x²−y², the addition counterpart to SUMX2MY2's subtraction.SUMXMY2Calculates the sum of (x−y)² for each pair, a fundamentally different calculation despite the similar name - subtraction happens before squaring, not after.SUMSQCalculates the sum of squares within a single set of values, rather than SUMX2MY2's comparison between two paired sets.Why does SUMX2MY2 return a #N/A error?
array_x and array_y probably don't have the same number of values - each element in one array needs a corresponding element in the other at the same position.
Can SUMX2MY2 return a negative number?
Yes, since it sums x²−y² across all pairs, the result will be negative whenever the y values are generally larger in magnitude than the corresponding x values.
How is SUMX2MY2 different from SUMXMY2?
SUMX2MY2 squares each value first, then subtracts (Σx² − Σy²... more precisely Σ(x²−y²)). SUMXMY2 subtracts each pair first, then squares that difference (Σ(x−y)²) - the order of operations produces very different results.
Need to translate a formula using SUMX2MY2?
Use our translator to convert your complete formula