SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 functions - Sum function contains squared values ​​in Excel

The following article details the total functions that contain squared values: SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2

SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 functions - Sum function contains squared values ​​in Excel Picture 1 SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 functions - Sum function contains squared values ​​in Excel Picture 2

1. The SUMSQ function

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

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

Inside:

- number1, number2 . are the values ​​to sum of squares, using up to 255 number values. Number parameters can be numbers, arrays, or referenced to numeric data.

Attention:

- If the number values ​​are not numbers -> the function returns the #VALUE! Error value

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

2. SUMXMY2 function

2.1. SUMXMY2 function

The function sums the difference of the square of the 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

The function performs the sum of squares of elements in the data array: SUMXPY2 (x, y) = Σ (x2 + y2) .

Syntax: = SUMX2PY2 (array_x, array_y) .

2.3. SUMXMY2 function

The function performs 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 2 data arrays that contain values ​​that need the sum of the difference of squares. array_x , array_y must be the same size.

Attention:

- If array_x , array_y are text values ​​that the function ignores are not counted.

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

- Name memorization: Basically all three functions are summing the functions of squared values. In all 3 functions only differ from 1 to 2 characters. Based on that difference to remember the function name:

+ Function SUMXMY2 : Contains the letter M means Minus = minus => Calculate the sum of the difference of squared values.

+ SUMX2PY2 function : Contains the letter P means Plus = Add => Calculate the sum of squared values.

+ SUMXMY2 function : Does not contain the number 2, the letter M is Minus -> Calculate the sum of squares.

3. Example of using the 4 functions above

- Summing squares using the SUMSQ function.

In the cell to calculate enter the formula = SUMSQ (B8: B12, C8: C12) -> Press Enter the result received is:

SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 functions - Sum function contains squared values ​​in Excel Picture 3

- Calculate the square of the squares using the SUMX2MY2 function .

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

SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 functions - Sum function contains squared values ​​in Excel Picture 4

Pressing Enter results returns:

SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 functions - Sum function contains squared values ​​in Excel Picture 5

- Calculate the sum of squares using the SUMX2PY2 function .

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

SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 functions - Sum function contains squared values ​​in Excel Picture 6

Pressing Enter results returns:

SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 functions - Sum function contains squared values ​​in Excel Picture 7

- Calculate the sum of squares of differences 2 values ​​using the SUMMY2 function .

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

SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 functions - Sum function contains squared values ​​in Excel Picture 8

Pressing Enter results returns:

SUMSQ, SUMXMY2, SUMX2MY2, SUMXPY2 functions - Sum function contains squared values ​​in Excel Picture 9

The above is how to use the sum function with square values. Although the function name looks very complicated, you may notice it a bit.

Good luck!