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

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

  1. Forecast against actual

    ABCD
    1ForecastActual
    2101214
    31514
    42023

    In D2: =SUMXMY2(A2:A4, B2:B4) → 14

    Try this example

  2. The root mean squared error

    ABCD
    1ForecastActual
    210122.16
    31514
    42023

    In D2: =SQRT(SUMXMY2(A2:A4, B2:B4)/ROWS(A2:A4)) → 2.16

    Try this example

Common mistakes

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.

Translate a whole formula

Try SUMXMY2 in the playground

Related functions