Excel like a finance pro.
← The libraryPractice · 2 questions →
Math function
SUMXMY2
Returns the sum of squares of differences of corresponding values in two arrays.
Rarely usedDifficulty 1400 · AdvancedUsage rank #361 of 520
When to use it
Sum of squared differences (x - y)² across two arrays. This is the sum of squared errors between a forecast and actuals, one function away from RMSE.
The shape of it
- Syntax
=SUMXMY2(array_x, array_y)
Worked examples
Two pairs
=SUMXMY2({2,3},{1,1}) → 5
1 + 4.
Sum of squared errors
=SUMXMY2({10,20,30},{12,18,33}) → 17
Forecast versus actual: 4 + 4 + 9.
RMSE
=SQRT(SUMXMY2(A2:A4,B2:B4)/COUNT(A2:A4)) → 2.380476143
RMSE for the same three pairs.
Worth knowing
- RMSE: =SQRT(SUMXMY2(actual,forecast)/COUNT(actual)).
- Euclidean distance between two points given as arrays: =SQRT(SUMXMY2(p1,p2)).
- Arrays must be the same size.
Where it goes wrong
- #N/A when sizes differ.
- Pairs with text or blanks are dropped, which changes the count for an RMSE.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.