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!
You should read it
- How to fix the SUM function doesn't add up in Excel
- Instructions for using Index function in Excel
- CHISQ.INV function - The function returns the inverse of the probability of the left end of the distribution when squared in Excel
- CHISQ.DIST - Function returns the distribution when squared in Excel
- Basic Excel functions that anyone must know
- MODE.MULT function - The function returns a vertical array of the most common values in Excel
- How to use the IFS function in Excel 2016
- How to use Excel's VALUE function
- How to use the kernel function (PRODUCT function) in Excel
- PHP functions
- How to combine Index and Match functions in Excel
- QUARTILE.INC function - The function returns the quartile of a dataset including values 0 and 1 in Excel
May be interested
ROUND, ROUNDDOWN, ROUNDUP functions - Functions for rounding decimal numbers in Excel
SLN function - Returns the depreciation value of an asset using the straight-line method in Excel
DDB function - Calculate depreciation of assets in Excel
MINVERSE function - The function returns the inverse matrix of a given matrix in Excel
CHIDIST function - Function returns the right end probability of the distribution when squared in Excel
COUPNCD - The function returns the next coupon date in Excel