PlanMaker SUMXMY2: Squared Differences and Prediction Errors

• Introduced: Documented by the PlanMaker 2010 manual; the original introduction release is not established.
• Availability: Documented in PlanMaker 2026. This identifies current documented support, not a final working-version endpoint.
• Scope: The reference belongs to the 2026/NX manual family. FreeOffice and individual mobile-build availability are not established here.
• Checked: October 14, 2026, against the official function reference. Expected outputs are vendor examples or independent calculations; no native PlanMaker test was performed.

=SUMXMY2(Range1, Range2)

Both inputs are required. The official reference defines the sum of (x-y)^2 for corresponding values. Both ranges must have the same number of rows and columns; a mismatch produces #N/A.

The subtraction happens before the square. This is not the same calculation as subtracting y² from x², which is handled by SUMX2MY2.

If either value in a pair is blank, text or a logical value, PlanMaker ignores that entire pair. Numeric zeros are included. That documented rule does not specify every possible source-error or coercion case.

Create a blank worksheet with x, y, Difference and Squared difference in A1:D1. Enter these values:

Enter =A2-B2 in C2 and =C2^2 in D2; fill C2:D2 through row 4. Put =SUMXMY2(A2:A4,B2:B4) in F2 and =SUM(D2:D4) in F3. Both expected results are 84.

For contrast, =SUMX2MY2(A2:A4,B2:B4) in F4 gives 120 on these data. Squaring the differences includes a cross term that is absent from a difference of squares.

Create a separate worksheet named Forecast. Use A1:D1 for Actual, Predicted, Error and Squared error. Enter 100, 105 in A2:B2, 120, 115 in A3:B3 and 80, 85 in A4:B4.

In C2 enter =A2-B2 and in D2 enter =C2^2; fill through row 4. Expected signed errors are -5, 5, -5, and their squares are 25, 25, 25.

In F2 enter =SUMXMY2(A2:A4,B2:B4); expect 75. In F3 enter =F2/3; expect 25, the mean squared error for these three complete pairs. In F4 enter =SQRT(F3); expect 5, the root mean squared error.

The three statistics have different scales: the first is a total, the second averages squared units, and the last returns to the input unit. Neither SUMXMY2 nor this division automatically validates a prediction model or selects the right sample for evaluation.

On Forecast, enter =SUM(C2:C4) in F6; expect -5. This signed total answers a different question from the squared-error sum of 75. A positive and negative error can cancel in the signed total, whereas both increase squared error.

For a clear cancellation control, put 5, 4 in H2:I2 and 4, 5 in H3:I3. Enter =SUMXMY2(H2:H3,I2:I3) in K2; expect 2. The signed differences 1 and −1 sum to zero, but the squared differences do not.

Swapping the two SUMXMY2 arguments reverses every difference’s sign before squaring, so the total remains unchanged. The reversed original-data call =SUMXMY2(B2:B4,A2:A4) therefore also has expected result 84.

Create a separate sheet named PairCheck. Put 9, 4, 7 in A2:A4, 1 in B2 and 5 in B4, leaving B3 empty. Enter =SUMXMY2(A2:A4,B2:B4) in D2. The documented exclusion rule gives an expected 68, from 64+4.

Replacing B3 with numeric 0 includes the middle pair and gives 84. Replacing it with the text missing excludes that pair again, giving an expected 68.

If computing a mean squared error from data with missing pairs, divide by the number of included complete numeric pairs, not blindly by the worksheet’s total row count. The three-pair Forecast example deliberately has no exclusions, which is why its divisor of three is valid.

An intentional =SUMXMY2(A2:A4,B2:B3) test has unequal dimensions and is documented to return #N/A. A column and a row containing the same number of cells still have different shapes. Supply ranges with equal height and width.

Even matching shapes can hide a modeling error if one column was sorted independently. Confirm that each observation and prediction refer to the same record. Squared distances also require compatible units.

The short reference does not give a complete source-error or precision table. For very large values or nearly equal measurements, compare full stored numbers and use an appropriate tolerance rather than inferring accuracy from displayed digits alone.

SUM reproduces the explicit error-column total. SQRT converts a mean squared error to its root. SUMX2MY2 and SUMX2PY2 perform different paired-square operations.