SUMX2PY2 function

The SUMX2PY2 function in Excel is used to calculate the sum of the sum of squares of corresponding values in two arrays or ranges. Essentially, it squares each value in the first array, squares each value in the second array, and then adds the squared values together. The function then sums these results.

This function is commonly used in situations such as statistical calculations, particularly when dealing with sums of squares in regression analysis, distance calculations, or other scenarios requiring squared values.

Syntax

=SUMX2PY2(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

• Squaring and Adding: The SUMX2PY2 function squares each value in array1, squares each value in array2, and then adds the squared values together. Finally, it sums these results.
• Array Size: The two arrays must have the same number of elements. If they are not of equal size, 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:

   =SUMX2PY2(A1:A3, B1:B3)
   

   Calculation:

   • Square of values in A1:A3: $2^2 = 4$, $4^2 = 16$, $6^2 = 36$
   • Square of values in B1:B3: $1^2 = 1$, $3^2 = 9$, $5^2 = 25$
   • Add the squares:
     • $4 + 1 = 5$
     • $16 + 9 = 25$
     • $36 + 25 = 61$

   Sum of these results: $5 + 25 + 61 = 91$

   Result: 91

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:

   =SUMX2PY2(A1:A4, B1:B4)
   

   Calculation:

   • Square of A1:A4: $1^2 = 1$, $2^2 = 4$, $3^2 = 9$, $4^2 = 16$
   • Square of B1:B4: $4^2 = 16$, $3^2 = 9$, $2^2 = 4$, $1^2 = 1$
   • Add the squares:
     • $1 + 16 = 17$
     • $4 + 9 = 13$
     • $9 + 4 = 13$
     • $16 + 1 = 17$

   Sum of these results: $17 + 13 + 13 + 17 = 60$

   Result: 60

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 squares the numbers before adding, so the result is unaffected by whether the numbers are positive or negative. For example, squaring -2 and 2 both yield the same result (4), and thus the function will work the same regardless of the sign of the numbers.
• Performance: SUMX2PY2 is typically used for advanced mathematical or statistical tasks, and is more computationally expensive than simpler functions like SUM. It is important to ensure the function is applied only when needed.

Related Functions

• SUMX2MY2: Similar to SUMX2PY2, but instead of adding the squares of corresponding values, it subtracts the square of the values in the second array from the square of the values in the first array.
• 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.
• SUMSQ: This function calculates the sum of the squares of a single array or range of values.
• SUM: Adds up the values in a range without squaring them.

The SUMX2PY2 function is useful for specialized mathematical operations, particularly in fields like statistics, engineering, or data science where squaring values and summing them is a necessary step in calculations like Euclidean distance, regression analysis, or other formula-based computations.