SUMX2MY2 function
SUMX2MY2 adds up the differences of squares of paired values, returning the sum of x squared minus y squared. It is a statistics helper, and both arrays must be the same size.
Example
Read it as a sentence: square each Y1:Y4 value and each Z1:Z4 value, subtract the Z squares from the Y squares, and add the differences, which gives -24.
Excel shows -24
Y
Z
1
1
2
2
2
3
3
3
4
4
4
5
Data
Syntax
SUMX2MY2(array_x, array_y)
-
array_x(required) - The first array.
-
array_y(required) - The second array, same size.
Returns: The sum of x squared minus y squared for each pair.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=SUMX2MY2({1,2},{3,4}) | -20 | Arrays |
=SUMX2MY2(3,2) | 5 | Scalars |
=SUMX2MY2({1,2},{3;4}) | -20 | Row vs column same count |
=SUMX2MY2(Data!Y1:Y4,Data!Z1:Z4) | -24 | Two ranges |
=SUMX2MY2(Data!AD1:AD3,Data!AB1:AB3) | 0 | Text in range skipped |
=SUMX2MY2(Data!Y1:Y4,Data!Z1:Z3) | #N/A | Size mismatch |
=SUMX2MY2(Data!D1:D3,Data!AB1:AB3) | #DIV/0! | Blank pair skipped |
=SUMX2MY2(Data!C1:C4,Data!Y1:Y4) | #N/A | Error |
The Data sheet these formulas read
A cell that starts with = holds that formula.
C
D
Y
Z
AB
AD
1
1
1
2
1
1
2
2
2
3
2
x
3
=NA()
3
4
3
3
4
4
4
5
Data
Watch out for
- Arrays must be the same size or you get #N/A.
Where it works
- Excel
- SUMX2MY2 is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Math and trig functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .