Summing Squares: SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 Functions in Excel

This article provides detailed guidance on functions to calculate sums of squares: SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2

1. SUMSQ Function

Description: The SUMSQ function is used to calculate the sum of squares of numbers.

Syntax: = SUMSQ(number1, number2, ...).

Where:

- number1, number2…are the values for which you want to calculate the sum of squares, with a maximum of 255 number values. The number parameters can be numbers, arrays of numbers, or references to numeric data…

Note:

- If the number values are not numeric -> the function returns the error value #VALUE!

- The formula to calculate the SUMSQ function (x, y) = x^2 + y^2.

2. Hàm SUMXMY2

2.1. Hàm SUMXMY2

Function to calculate the sum of the difference of two squares of corresponding elements from left to right in the data array SUMX2MY2(x, y) = Σ(x2-y2).

Syntax: = SUMX2MY2(array _x, array_y).

2.2. SUMX2PY2 Function

Function to calculate the sum of squares of elements in the data array: SUMXPY2(x, y) = Σ(x2+ y2).

Syntax:

2.3. SUMXMY2 Function

Function to calculate the sum of squares of elements in the array: SUMXMY2(x, y) = Σ(x - y)2.

Syntax: = SUMXMY2(array_x, array_y).

In all 3 functions above:

- array_x, array_y are two data arrays containing values to calculate the sum of the differences of squares. array_x, array_y must have the same size.

Note:

- If array_x, array_y are text values, the function ignores them.

- If array_x, array_y do not have the same size -> the function returns N/A.

- Memorization technique: Essentially, all 3 functions above are for calculating the sum of squared values. The difference among them is just 1 to 2 characters. Use this distinction to remember their names:

+ Function SUMXMY2: Contains the letter M meaning Minus = subtract => Calculate the sum of the differences of squares.

+ Function SUMX2PY2: Contains the letter P meaning Plus = Add => Calculate the sum of squares.

+ SUMXMY2 Function: Does not contain the number 2, contains the letter M which stands for Minus -> Calculates the sum of squared differences.

3. Examples using the 4 functions above

- Calculate the sum of squares using the SUMSQ function.

In the cell where you want to calculate, enter the formula =SUMSQ(B8:B12,C8:C12) -> Press Enter and you will get the result:

- Calculate the difference of squares using the SUMX2MY2 function.

In the cell where you want to calculate, enter the formula: =SUMX2MY2(B8:B12,C8:C12).

Press Enter to get the result:

- Calculate the sum of squares using the SUMX2PY2 function.

In the cell where you want to calculate, enter the formula: =SUMX2PY2(B8:B12,C8:C12).

Press Enter to get the result:

- Calculate the sum of squares of the difference of two values using the SUMMY2 function.

In the cell where you want to calculate, enter the formula: =SUMXMY2(B8:B12,C8:C12).