Browsing all articles tagged with UMXMY2

SUMXMY2 Excel Formula

Author Zaheer    Category Formulas     Tags ,

SUMXMY2 function returns the sum of the squares of differences of the corresponding value in 2 arrays. @array is first array of variety of data points and the @array2 is second array or the variety of data points. The equation of SUMXMY2 is the Sum (X – Y) ^ 2. Strings and empty cells are just ignored.  If @array 1 and the @array 2 has various number of data points, SUMXMY2 return #N/A error.

This function is an EXCEL compatible.  SUMXMY2


= SUMXMY2 (array1, array2)


The syntax for the SUMXMY2 function is in this manner with arguments specified with in the parentheses.

= SUMXMY2 (array1, array2)


‘array_1’ is the needed arguments and it is an array number one or range of values

‘array_2’ is the needed arguments and it is an array number of two or range of values


The array_1 and the array_2 should contain similar number of values if not the SUMXMY2 function return the # N/A error value. The argument to acquire the summation of squares of difference is in this manner.

= SUMXMY2=? (x-y) 2


The arguments must be numbers or arrays, names, or a reference that contains numbers.

  • If the array or the reference argument contains logical values, text, or empty cells, the values are disregarded; on the other hand, cells with value zero are incorporated.
  • If array_ 1 and array_ 2 have different numbers of value, the SUMXMY2 returns to #N/A error value.
  • The equation for sum of the squared differences would be:

GAMMALN = LN (? (x))

How to Use SUMXMY2 Excel Function?

SUMXMY2 function of EXCEL computes the sum of squares of differences between 2 sets of values. This is named subsequent to mnemonic SUM of the (X -Y) ^2″ and found in the statistical formulas.

  • Study the syntax of the SUMXMY2 function. It is SUMXMY2 (array_1, array_2) where the array_1 is an array for the 1 values and the array_2 is the array for the 2 values.
  • Make use of arrays, numbers and names for the argument to SUMXMY2 function. Cells in the array that contains logical values, empty cells and texts will be disregarded. However, when the cells contain zero value it would be included. SUMXMY2 function will return to the #N/A error value, when the array_1 doesn’t have the same number of value as array_2.
  • Compute the return value for the SUMXMY2 function. It would be: SUM (X-Y) ^ 2 for every (X, Y) pair in argument list
  • Enter the next variables below in the first 2 columns of the Excel spreadsheet for the simple example of the SUMXMY2 function:

Row 1: array_1 and array_2

Row 2: 4 and 7

Row 3: 2 and 9

=SUMXMY2 (A1:A2, B1:B2) is assessed as (4 – 7) ^2 plus (2 – 9) ^2 equals (-3) ^2 plus (-7) ^2 equals 9 plus 49 equals 58. Keep in mind that how the array_1 is in first column and array_2 is in second column.

  • Look at the subsequent pattern of SUMXMY2 function with the use of numbers as the arguments:

=SUMXMY2 ({4, 7}, {2, 9}) will go back to 58. Remember the use of the curly braces to describe the 2 arrays.