How to Measure Forecast Error with SUMXMY2 in Excel

SUMXMY2 adds the squared differences between two ranges — compare forecasts against what really happened without a helper column.

You will learn to

• Total the squared errors of a forecast
• Turn them into mean squared error
• Pick the more accurate of two forecasts

How it works

SUMXMY2(array_x, array_y) subtracts one range from the other row by row, squares each difference and adds them up — the “sum of x minus y squared”. =SUMXMY2(B2:B8,D2:D8) gives the total squared error of a forecast in B against actuals in D, with no error column needed.

Divide by the number of periods for the mean squared error, or take its square root for RMSE. Because the differences are squared, the order of the two ranges does not matter, and a few large misses count much more than many small ones. Comparing SUMXMY2 for two forecasts tells you which one tracked reality more closely.

=SUMXMY2(B2:B8,D2:D8)