The `SUMX2PY2` function in Excel is used to sum the sums of squares of corresponding values in two arrays. It calculates the sum of the sum of squares, which means it adds the squares of values in the two arrays together and then sums the total.
Here’s how to use the `SUMX2PY2` function:
Syntax
SUMX2PY2(array_x, array_y)
- array_x: This is the first array or range of numeric data.
- array_y: This is the second array or range of numeric data.
Both “array_x” and “array_y” should have the same size because the function works on pairs of values.
Example
Suppose you have the following data in Excel:
| A | B |
|——-|——-|
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
You want to compute the sum of the sums of squares of the numbers in these two columns.
=SUMX2PY2(A1:A3, B1:B3)
- Enter the following formula in a cell where you want the result:
- Press Enter.
Explanation
For the above example, the function computes:
- (1^2 + 2^2) + (3^2 + 4^2) + (5^2 + 6^2)
- Which breaks down to: (1 + 4) + (9 + 16) + (25 + 36)
- Resulting in: 5 + 25 + 61 = 91
Notes
- Ensure that both arrays have the same number of elements; otherwise, Excel will return a `#VALUE!` error.
- Both arguments must contain numeric data; any text, logical values, or empty cells in the arrays will result in an error.
That’s how you can use the `SUMX2PY2` function in Excel to calculate the sum of the sums of squares of corresponding values in two arrays.
Unlock Your Potential
Excel
Access
Power BI
