Excel functions / Math
SUMXMY2 function
Adds up the squares of the differences of matching values in two ranges.
Syntax
=SUMXMY2(array_x, array_y)
Arguments
array_x: The first range.array_y: The second range, the same size.
When to use it
SUMXMY2 squares the difference between each pair of values in two lists and adds them up. It is the core of a squared distance between two sets of numbers, or of how far forecasts were from what happened.
Examples
Forecast against actual
A B C D 1 Forecast Actual 2 10 12 14 3 15 14 4 20 23 In D2:
=SUMXMY2(A2:A4, B2:B4)→ 14The root mean squared error
A B C D 1 Forecast Actual 2 10 12 2.16 3 15 14 4 20 23 In D2:
=SQRT(SUMXMY2(A2:A4, B2:B4)/ROWS(A2:A4))→ 2.16
Common mistakes
- Different sizes. The two lists must be the same size.
SUMXMY2 in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: SUMXMY2
- Danish: SUMXMY2
- Dutch: SOM.XMINY.2
- Finnish: EROTUSTEN.NELIÖSUMMA
- French: SOMME.XMY2
- German: SUMMEXMY2
- Hungarian: SZUMXBŐLY2
- Italian: SOMMA.Q.DIFF
- Norwegian: SUMMERXMY2
- Polish: SUMA.XMY.2
- Portuguese (Brazil): SOMAXMY2
- Portuguese (Portugal): SOMAXMY2
- Russian: СУММКВРАЗН
- Spanish: SUMAXMENOSY2
- Swedish: SUMMAXMY2
- Turkish: TOPXEY2