SUMXMY2
Returns the sum of squares of differences of corresponding values in two arrays: Σ(x − y)². Common in least-squares analysis.
SUMXMY2(array_x, array_y)Arguments
array_xThe first array or range of values.array_yThe second array or range of values (must match array_x).
=SUMXMY2({2,3,4},{1,2,3})(2-1)² + (3-2)² + (4-3)² = 3
=SUMXMY2(A1:A3,B1:B3)Σ(Aᵢ − Bᵢ)²
- •Returns #N/A if the two arrays have different sizes
- •Often used to compute residual sum of squares (RSS) in regression
- •Empty cells, logical values and text are ignored
- •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 SUMXMY2 (sum of squared differences, (x−y)² for each pair) with SUMX2MY2 (sum of x²−y², squaring happens before subtracting) - despite the similar names, the order of subtracting and squaring is completely different
- •Expecting the result to ever be negative - since each term is a squared difference, SUMXMY2's total is always zero or positive, unlike SUMX2MY2 which can go negative
SUMX2MY2Calculates the sum of x²−y² instead of (x−y)², a fundamentally different calculation despite the similar name - squaring happens before subtracting, not after.SUMX2PY2Calculates the sum of x²+y² for each pair, another related but distinct comparison between two arrays.SUMSQCalculates the sum of squares within a single set of values, rather than SUMXMY2's paired-difference comparison between two sets.Why does SUMXMY2 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 SUMXMY2 return a negative number?
No, since every term is a squared difference (x−y)², and squares are always non-negative, the total is always zero or positive.
What is SUMXMY2 commonly used for?
It's frequently used to calculate the residual sum of squares in regression analysis - measuring the total squared distance between predicted and actual values.
Need to translate a formula using SUMXMY2?
Use our translator to convert your complete formula