SUMX2PY2

What it does

SUMX2PY2(array_x, array_y)

SUMX2PY2(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: [0, +infinity). Every term is non-negative, so the sum is monotone in every input's magnitude and is zero only when all contributing values are zero.
• Interpretation: SUMX2PY2(x, y) = ||x||^2 + ||y||^2 — the total squared Euclidean norm of the two vectors taken together. Equivalently it is SUMSQ applied to the concatenation of the two arrays, which is the cleanest way to think about it: the pairing does not actually matter to the answer. Addition is commutative and every term enters positively, so any re-pairing of the same multiset of values gives the same mathematical result. That property is unique to this member of the trio and it has a practical consequence noted below.
• Identities: SUMX2PY2(x, y) = SUMSQ(x) + SUMSQ(y); SUMX2PY2(x, y) = SUMXMY2(x, y) + 2*SUMPRODUCT(x, y), which is the polarization identity and the reason this function turns up in regression bookkeeping.

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

Arguments

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

Because the pairing is mathematically irrelevant here, the ambiguity that matters for SUMX2MY2 and SUMXMY2 — whether ignoring a value in one array also drops its partner — has a visible consequence for this function too, but of a different kind. If ignoring is pairwise, a text cell in array_x silently removes a perfectly good number from array_y as well, and the total drops by that number's square. If ignoring is independent, the total keeps it. The documentation does not say which, and the two readings give different numbers. It is the first probe below.

The other commonly misunderstood point is the length check: the function requires equal counts even though the answer would be well defined for unequal ones. The requirement is structural, not mathematical.

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.
• Zero values are included, per the documented remark.
• Overflow. The characteristic edge. The squares carry twice the exponent range of the inputs, so a single term overflows once |x| passes roughly the square root of the largest double — even though a scaled computation would return a finite answer. Because all terms are positive, overflow is monotone and irreversible: once the accumulator saturates there is no later term that can bring it back.
• Underflow. Symmetrically, values below about 1e-160 square to subnormals and then to zero, so a vector of small entries can return exactly zero when the true sum of squares is a representable positive number.
• Empty or all-ignored input: not documented; zero is the family-consistent answer and the Handbook has not checked.

Errors

The documented ignore-list names text, logicals and empty cells; error values are not mentioned, so their treatment is unstated by the documentation.

How to implement

Numerical notes

This is the placid member of the trio. There is no cancellation — every term is non-negative, so the running sum is monotone and the relative error of the result is bounded by the accumulated rounding of the additions. Everything difficult about SUMX2PY2 is about range, not about accuracy.

1. Dynamic range is the whole problem. Squaring doubles the exponent, so a set of inputs spanning the ordinary working range produces squares spanning twice that, and the two ends of that span behave badly:

• Above the square root of the largest double, a term overflows on its own.
• Below roughly the fourth root of the smallest normal, a term underflows to zero, silently discarding a contribution.

The remedy is the standard scaled accumulation from Blue's TOMS algorithm and the LAPACK dnrm2 line: find the largest magnitude m over both arrays, accumulate SUM (v/m)^2, and multiply by m^2 at the end. Blue's three-accumulator refinement handles data that spans the whole range in a single pass. This removes both bands exactly, and it costs one extra pass or a running maximum. Nothing in the interface tells you whether it is done, and probe 3 below is the way to find out.

3. Squaring is exact in the significand, inexact in the result. x*x carries one rounding, which is the minimum possible. There is no accuracy to be gained by a cleverer squaring; the gains are all in the scaling and the summation order.

4. What a careful implementation does: scale by the global maximum magnitude, accumulate pairwise or with compensation, unscale once. Where the data is well inside the normal range the result is indistinguishable from the naive one, which is why this function looks easy — the difference only appears at the ends, and it appears as the difference between an answer and an error.

Rust code

47 sumx2py2 "SUMX2PY2" (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

• SUMSQ — SUMX2PY2(x, y) = SUMSQ(x) + SUMSQ(y), exactly. Since SUMSQ is variadic and accepts both ranges in one call, SUMSQ(x, y) computes the same mathematical quantity with a different (and looser) length requirement. The two differ only in the structural check and in the summation order.
• SUMX2MY2 — the same construction with a minus. That one cancels; this one cannot. If you only read one paragraph of the trio's numerical notes, read SUMX2MY2's.
• SUMXMY2 — the sum of squared differences; related to this function by SUMXMY2 = SUMX2PY2 - 2*SUMPRODUCT(x, y).
• SUMPRODUCT — the general member, with the differing documented mismatch code.
• SQRT(SUMX2PY2(x, y)) — the Euclidean norm of the concatenated vectors, and the idiom to reach for given that Excel has no norm function.
• Confused with: SUMX2MY2 and SUMXMY2, whose names differ only in the placement of a letter. A useful reading rule: everything before the final 2 describes the expression; the trailing 2 means "squared". SUMX2PY2 is x²+y²; SUMXMY2 is (x−y)².

Named variants

No named variants are recorded yet.