SUMXMY2 function

The SUMXMY2 function in Excel calculates the sum of the squares of the differences between corresponding values in two arrays or ranges. In other words, it subtracts each value in the second array from the corresponding value in the first array, squares the difference, and then sums all these squared differences.

This function is often used in statistical and mathematical contexts, such as calculating the squared differences between two sets of data or finding the total squared error in regression analysis.

Syntax

=SUMXMY2(array1, array2)

Parameters

1. array1 (required): The first array or range of numbers.
2. array2 (required): The second array or range of numbers.

Both arrays must have the same number of elements; otherwise, Excel will return an error.

Key Points

• Difference Squared: The function calculates the difference between each value in array1 and array2, squares the differences, and then sums them.
• Array Size: Both arrays must have the same number of elements. If they don’t, Excel will return an error.

Example

1. Basic Example with Two Ranges (A1:A3 and B1:B3): Let’s say you have the following data:
   • A1:A3 contains the values: 2, 4, and 6
   • B1:B3 contains the values: 1, 3, and 5

   The formula:

   =SUMXMY2(A1:A3, B1:B3)
   

   Calculation:

   • Difference between A1 and B1: $2 – 1 = 1$
   • Difference between A2 and B2: $4 – 3 = 1$
   • Difference between A3 and B3: $6 – 5 = 1$

   Squaring the differences:

   • $1^2 = 1$
   • $1^2 = 1$
   • $1^2 = 1$

   Sum of squared differences: $1 + 1 + 1 = 3$

   Result: 3

2. Another Example with Larger Arrays (A1:A4 and B1:B4): If A1:A4 contains the values 1, 2, 3, and 4, and B1:B4 contains the values 4, 3, 2, and 1:

   =SUMXMY2(A1:A4, B1:B4)
   

   Calculation:

   • Difference between A1 and B1: $1 – 4 = -3$
   • Difference between A2 and B2: $2 – 3 = -1$
   • Difference between A3 and B3: $3 – 2 = 1$
   • Difference between A4 and B4: $4 – 1 = 3$

   Squaring the differences:

   • $(-3)^2 = 9$
   • $(-1)^2 = 1$
   • $1^2 = 1$
   • $3^2 = 9$

   Sum of squared differences: $9 + 1 + 1 + 9 = 20$

   Result: 20

Notes

• Array Size: Both array1 and array2 must contain the same number of cells. If they don’t, Excel will return an error (e.g., #VALUE!).
• Negative Numbers: The function works with both positive and negative values. The difference is squared, so the sign of the difference does not matter.
• Use in Statistics: The SUMXMY2 function is often used in statistical analysis, especially when calculating error terms, such as in the sum of squared errors (SSE) for regression analysis.

Related Functions

• SUMSQ: This function calculates the sum of the squares of a single range of numbers, without considering any differences.
• SUMX2MY2: Similar to SUMXMY2, but it squares the numbers in array1 and array2 first, and then subtracts the square of the second array from the square of the first array.
• SUMX2PY2: Similar to SUMXMY2, but instead of subtracting the squared differences, it adds the squares of corresponding elements from two arrays.
• SUMPRODUCT: A more general function that multiplies corresponding values in arrays and then sums the products. It can also be used for conditional summations and weighted sums.

The SUMXMY2 function is particularly useful in fields such as data analysis, machine learning, and statistics, where you need to calculate the squared differences between two sets of data points.