SUMX2MY2

What it does

SUMX2MY2(array_x, array_y)

SUMX2MY2(array_x, array_y) returns

SUM over i of (x_i^2 - y_i^2)

• Domain: two equal-length lists of real numbers. Microsoft documents that unequal lengths give #N/A.
• Range: all reals. Unlike SUMSQ and SUMX2PY2, this aggregate is signed and can be zero for non-zero data — indeed it is zero whenever the two vectors have the same Euclidean norm, which is exactly the case a user is most likely to be testing.
• The identity that defines the page: x^2 - y^2 = (x - y)(x + y). Algebraically trivial, numerically decisive; see Numerical notes.
• Interpretation: SUMX2MY2(x, y) = ||x||^2 - ||y||^2, the difference of squared Euclidean norms. It appears in energy-difference calculations, in the numerator of certain variance-ratio statistics, and in physics worksheets computing E = m(c^2 - v^2)-shaped quantities.

Microsoft's page states the equation as the sum of x^2 - y^2 over corresponding pairs.

Arguments

The commonly misunderstood aspect is pairing. The two arrays are matched element by element in their natural order, so a row range and a column range of the same length pair up positionally, not geometrically. The documentation speaks of "a different number of values" rather than of shape, which suggests count rather than dimension is what is compared — but it does not say so explicitly, and the Handbook does not assert it.

The scan rule is documented: "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."

That remark hides a real question the documentation does not answer. If a value in array_x is ignored, is its partner in array_y also dropped, or does the pairing shift? Two readings are possible — pairwise exclusion (drop both, keep the alignment) and independent compaction (each array compacts separately, re-pairing surviving elements) — and they give different answers on the same data. Nothing on the page settles it. It is the first probe below.

Try it

Quirks

Result and edge cases

Returns Number.

• Length mismatch: #N/A, as documented. Note the code: #N/A, not #VALUE!.
• Empty pairs / all values ignored: not documented. Zero is the family-consistent answer; the Handbook has not checked.
• Zero values are included, per the documented remark, which matters because a zero contributes nothing to the sum but does occupy a pairing slot.
• Overflow: the squares carry twice the exponent range of the inputs, so a term can overflow even where the mathematical result is small. This is SUMSQ's hazard, inherited.
• Cancellation: the defining hazard of this surface. Discussed below.

Errors

The documented ignore-list covers text, logicals and empty cells; it does not mention error values, so their treatment here is unstated by the documentation.

How to implement

Numerical notes

This is a cancellation function. When x_i and y_i are close, x_i^2 and y_i^2 are closer still in relative terms, and their difference loses roughly as many significant bits as the two values share. In the extreme — x and y agreeing to within a few ULP — the computed difference of squares can be entirely rounding error. Since the natural use of this function is to compare two vectors of similar magnitude, the cancellation case is the intended case, not a pathological one.

The remedy is one line of algebra. Evaluate each term as

(x_i - y_i) * (x_i + y_i)

rather than as x_i*x_i - y_i*y_i. This is not a heuristic; it is provably better:

• x - y is computed exactly whenever y/2 <= x <= 2y (Sterbenz's lemma), which covers exactly the near-cancellation regime that destroys the naive form. The subtraction that loses information in the naive formula loses none in the rewritten one, because it happens before the squaring rather than after.
• x + y is a well-conditioned sum for same-sign data and carries at most one rounding.
• The product then carries at most one more rounding. The rewritten term has a small relative error where the naive term has an unbounded one.

The rewrite also improves the overflow picture: x^2 overflows for |x| above roughly the square root of the largest double, while (x-y)(x+y) overflows only when the answer itself is near the top of the range. There is a whole band of inputs on which the naive form fails and the rewritten form returns the right answer.

Kahan's work on difference-of-products (and the fma-based two-product technique that goes with it) is the general treatment; the difference of squares is its simplest instance.

What a careful implementation does: rewrite each term as (x-y)(x+y), accumulate pairwise or with compensation, and — if the input range is wide — scale. The result will differ from a naive implementation, and that difference is largest exactly where users care most. The Handbook makes no claim about which form Excel uses; probe 2 below settles it.

Rust code

46 sumx2my2 "SUMX2MY2" (2, 2) [R "array_x", R "array_y"] |s, a, sv| {
    sum_pairwise(s, a, sv, |x, y| x * x - y * 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

• SUMX2PY2 — the same construction with a plus. Numerically it is the easy one, because the terms cannot cancel.
• SUMXMY2 — the sum of squared differences. Note the algebra: SUMXMY2 = SUMX2PY2 - 2*SUMPRODUCT(x, y), and SUMX2MY2 = SUMPRODUCT(x-y, x+y). The three functions plus SUMPRODUCT span the same quadratic forms in different arrangements.
• SUMPRODUCT — the general member, and the one whose documented mismatch error code differs (see above). SUMX2MY2(x, y) and SUMPRODUCT((x-y), (x+y)) are the same mathematical value by two routes and are the basis for the accuracy comparison below.
• SUMSQ — SUMX2MY2(x, y) = SUMSQ(x) - SUMSQ(y), which is the worst way to compute it and is exactly the composition this function should save you from.
• F.TEST and the variance-ratio statistics — the consumers of a difference of sums of squares.
• Confused with: SUMXMY2, constantly. The names differ by the position of a single letter and the functions differ by which operation is inside the square. SUMX2MY2 is "sum of (x-squared minus y-squared)"; SUMXMY2 is "sum of (x minus y), squared".

Named variants

No named variants are recorded yet.