MODE.MULT

What it does

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

MODE.MULT(number1,[number2],...) returns all values tied for the highest repetition count, as a vertical array. It finds a repeated value, not a middle value or an average.

Try it

=MODE.MULT({2,5,2,5,9}) returns a vertical array containing two and five: each occurs twice, while nine occurs once.

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. Modern Excel needs space for the vertical result to spill.

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 all keys attaining that count in a single-column array. 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

8 mode_mult "MODE.MULT" (1, 255) [R "number1", R "number2"] |s, a, sv| {
    let found = modes(&all(s, a, sv, Held::ModeNumbers)?);
    if found.is_empty() {
        return fail(WorksheetErrorCode::NA);
    }
    let rows: Vec<Vec<f64>> = found.into_iter().map(|m| vec![m]).collect();
    out_matrix(s, &rows)
}

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 one mode. MEDIAN gives the middle value and AVERAGE the arithmetic mean; neither needs a repeated observation.

Named variants

No named variants are recorded yet.