How to use the Excel SUMX2MY2 function -

Summary

The Excel SUMX2PY2 function returns the sum of the difference of squares of corresponding values in two arrays. Values can be supplied as constants, cell references, or ranges.

Purpose

Sum of difference of squares in two arrays

Return value

Calculated sum of difference of squares

Syntax

=SUMX2MY2 (array_x, array_y)

Arguments

  • array_x - The first range or array containing numeric values.
  • array_y - The second range or array containing numeric values.

Version

Excel 2003

Usage notes

The SUMX2MY2 function returns the sum of the difference of squares of corresponding values in two arrays. The "m" in the function name stands for "minus", as in "sum x2 minus y2". SUMX2MY2 can accept values as constants, references or ranges.

Examples

=SUMX2MY2((0,1),(1,2)) // returns -4 =SUMX2MY2((1,2,3),(1,2,3)) // returns 0

In the example shown above, the formula in E5 is:

=SUMX2MY2(B5:B12,C5:C12)

which returns -144 as a result.

Equation

The equation used to calculate the sum of the sum of squares is:

This formula can be created manually in Excel with the exponentiation operator (^) like this:

=SUM((range1^2)-(range2^2))

With the example as shown, the formula below will return the same result as SUMX2MY2:

=SUM((B5:B12^2)+(C5:C12^2)) // returns -144

Notes

  • Arguments can any mix of constants, names, arrays, or references that contain numbers.
  • Text values are ignored, but cells with zero values are included.
  • SUMX2MY2 returns #N/A if the arrays contain different numbers of cells.

Zajímavé články...