MODE

What it does

MODE(number1, [number2], ...)

MODE(number1,[number2],...) returns one value with the highest repetition count. This is the compatibility function; MODE.SNGL is the explicit single-result name. It finds a repeated value, not a middle value or an average.

Try it

=MODE({5,2,5,7}) returns 5, which occurs twice. The result is one of the observations, not their average.

Quirks

The recorded collection rule takes numbers only, including when text or logicals are typed directly. It skips numeric-looking text and TRUE rather than converting them. Thus MODE("2",2) and MODE(TRUE,1) have only one admitted number and give #N/A; MODE("x",2,2) still returns two.

A data set without any repeated number gives #N/A. Numeric zero participates. Error values propagate, with the first recorded error winning; arbitrary text is skipped rather than turned into a conversion error. A tied data set can have several modes although this function returns one.

How to implement

Collect numeric values only from direct arguments and held collections; skip text and logical values, and propagate errors. Normalize positive and negative zero as one key, and count occurrences. Track each key’s first occurrence. Find the largest count; if it is below two, return #N/A. Return a key attaining that count. The current code orders tied modes by first appearance and uses that order for the single-result choice. That is the implementation’s tie policy, not a whole-domain identification from the public function page. Keep the tie case in compatibility tests.

Rust code

6 mode "MODE" (1, 255) [R "number1", R "number2"] |s, a, sv| {
    match modes(&all(s, a, sv, Held::ModeNumbers)?).first() {
        Some(m) => s.number(*m),
        None => fail(WorksheetErrorCode::NA),
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.

Alternatives

MODE.SNGL returns a single mode; MODE.MULT returns tied modes; MEDIAN measures a different kind of centre.

Named variants

No named variants are recorded yet.