SUMXMY2

What it does

SUMXMY2(array_x, array_y)

SUMXMY2(array_x, array_y) returns

SUM over i of (x_i - y_i)^2

• Domain: two equal-length lists of real numbers. Microsoft documents that arrays with different numbers of values give #N/A.
• Range: [0, +infinity). Zero exactly when the two arrays agree elementwise.
• Interpretation: the squared Euclidean distance between the two vectors, ||x - y||^2. This is the residual sum of squares of regression, the objective function of least squares, the numerator of the mean squared error, and the quantity minimized by every curve fit in the statistics category. Of the three paired-array functions it is by far the most used, and it is the one with a genuine claim to being a primitive rather than a convenience.
• Identities: SUMXMY2(x, y) = SUMX2PY2(x, y) - 2*SUMPRODUCT(x, y) (the polarization identity); SUMXMY2(x, y) = SUMSQ(x-y) if the difference is formed first; SQRT(SUMXMY2(x, y)) is the Euclidean distance.
• Metric properties: SUMXMY2 is symmetric in its arguments and vanishes only on equality, but it is not a metric — the triangle inequality holds for its square root, not for it. Nothing in the function enforces or checks this; it matters when the result is fed into clustering or nearest-neighbour logic that assumes a metric.

Microsoft's page states the equation as the sum of squared differences and gives a worked example over two seven-element arrays.

Arguments

The documented scan rule is the trio's: "If an array or reference argument contains text, logical values, or empty cells, those values are ignored. However, cells with the value zero are included."

The pairing gap matters more here than anywhere else in the trio. For SUMX2PY2 the pairing is mathematically irrelevant; for SUMX2MY2 it changes the answer; for SUMXMY2 a mis-alignment is catastrophic, because the whole point of the function is that x_i and y_i are the same observation measured two ways. If ignoring a text cell in array_x compacts that array independently, every subsequent pair is shifted by one and the result is the distance between two vectors that were never meant to be compared — a large, plausible-looking number with no diagnostic. The documentation does not say which reading applies. This is the first probe below and it is the most consequential unstated behaviour on the page.

The second misunderstood point is the order of operations, encoded in the name: the subtraction happens before the squaring. SUMXMY2 is sum of (x - y) squared, not sum of x - (y squared) and not sum of (x squared) - (y squared) — the last of which is SUMX2MY2, a different function with a nearly identical name.

Try it

Quirks

Result and edge cases

Returns Number, always non-negative.

• Length mismatch: #N/A, as documented — not #VALUE!. See the family divergence below.
• Identical arrays: exactly zero, and exactly zero is achievable here because each term is the square of an exactly-computed difference (see Numerical notes).
• Zero values are included, per the documented remark.
• Overflow: a difference near the top of the range squares to overflow, though this requires inputs that are themselves enormous — less reachable than for SUMSQ, because the difference is usually much smaller than the operands.
• Underflow: a very small difference squares to zero. For nearly-equal arrays every term can underflow individually while the true sum is representable, which is the mirror image of the overflow case and the more likely one in practice.
• Empty or all-ignored input: not documented; zero is the family-consistent answer and the Handbook has not checked.

Errors

Error values are not named in the documented ignore-list, so their treatment is unstated by the documentation.

How to implement

Numerical notes

SUMXMY2 is, perhaps surprisingly, the best conditioned of the three paired-array functions, and the reason is a small theorem worth stating.

The subtraction is usually exact. Sterbenz's lemma: if y/2 <= x <= 2y (same sign, within a factor of two), then x - y is computed exactly in binary floating point — no rounding at all. The regime where a subtraction would normally be feared, near-equal operands, is precisely the regime where it is free of error. And near-equal operands are the normal case for this function: x and y are two measurements of the same thing.

So the term (x - y)^2 carries, in the common case, exactly one rounding (from the squaring), and the relative error of each term is at most one unit of roundoff. Compare SUMX2MY2, where the same near-equality destroys the term entirely. The difference between the two functions is not a matter of degree; it is the difference between an operation that loses no bits and one that can lose all of them. A reader choosing between the two formulations of "how far apart are these vectors" should choose this one for that reason alone.

Underflow of the terms. For two nearly identical arrays, each difference may be around 1e-170, whose square is subnormal or zero. The sum then reads zero even though the true value is a representable positive number. If the caller is using SUMXMY2 as a convergence test, this underflow is a silent success signal — the worst possible failure mode for that use. Scaled accumulation (Blue) removes it; so does testing on SQRT(SUMXMY2(...))-scale quantities rather than on the squared value.

What a careful implementation does: compute the differences first (exactly, in the common case), scale if the differences span a wide range, and accumulate pairwise or with compensation. The Handbook makes no claim about what Excel does.

Rust code

48 sumxmy2 "SUMXMY2" (2, 2) [R "array_x", R "array_y"] |s, a, sv| {
    sum_pairwise(s, a, sv, |x, y| (x - y) * (x - y))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• SUMX2MY2 — the near-homograph, and a genuinely different function: subtraction after squaring rather than before. That one cancels catastrophically; this one does not. Confusing the two is the most likely user error in this corner of the category.
• SUMX2PY2 — the third member, and the other half of the polarization identity.
• SUMPRODUCT — the general member, with the differing documented mismatch code. Note that SUMPRODUCT((x-y)^2) computes the same value and is what a reader writes when they have not found SUMXMY2.
• DEVSQ — the one-array analogue: SUMXMY2 against a constant mean is DEVSQ.
• STEYX, LINEST, TREND, RSQ, FORECAST.LINEAR — the regression consumers. Every one of them minimizes or reports a quantity of this shape, which is why this function exists.
• SQRT(SUMXMY2(x, y)) — Euclidean distance, the composition to use for nearest-neighbour and clustering work.
• Confused with: SUMX2MY2, as above; and with a mean squared error, which requires dividing by the count and which this function does not do.

Named variants

No named variants are recorded yet.