MODE.SNGL

What it does

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

MODE.SNGL(number1, [number2], …) returns a most frequently occurring value in the data.

Write the admitted values as a multiset S over the reals, and let m(v) be the multiplicity of v in S. Define

M = max { m(v) : v in S }
A = { v in S : m(v) = M }        the set of modes

Then:

• if M = 1 — every value occurs once — the mode is not defined and the function reports #N/A;
• if M > 1 and |A| = 1, the answer is the unique element of A;
• if M > 1 and |A| > 1, the mathematics does not pick a winner and the function must.

That third line is the whole substance of this page. The mode is the one classical measure of central tendency that is not a function of the data in the ordinary sense: it is set-valued. MEDIAN resolves its even-n ambiguity by a stated rule (average the two middle values). MODE.SNGL resolves a genuine multiplicity of maxima by an unstated one, and the selection rule is the fact a reader most needs and is least likely to find written down.

The mode is also not a statistic of the underlying distribution in any stable sense. For a continuous population the sample mode is almost surely #N/A (no value repeats), and where it is not, it is an artefact of rounding or of a measurement grid rather than of the distribution. MODE.SNGL is a function about the recorded data, not about the process that produced it.

Arguments

Which values reach the multiset is not the usual worksheet asymmetry: the function takes only numbers from its arguments, even when text or a logical is typed directly into the call (FN-MODE-001). See Coercion and lifting, which states the usual asymmetry once and explains why there is deliberately no global precedence rule.

Try it

Quirks

Result and edge cases

Returns Number.

• No repeated value. The documented result is #N/A. This is the common case for measured data and it is not an error in the sense of a mistake; it is the correct report that the sample has no mode.
• Ties among several modes. The selection rule is not stated on Microsoft's page and this Handbook does not know it. The two natural candidates are smallest tied value and first tied value in argument order, and they are distinguishable with a two-argument probe. See the probe list below.
• Arrays. MODE.SNGL is an aggregate, not a lift kernel: an array argument is consumed by scanning, and the function returns a scalar.

Errors

The #N/A row is documented by Microsoft on the page cited in Sources. Retrieval of that page was refused by the upstream host while this page was written, so the row is published as documented behaviour with its source named and should be re-checked against the page. The propagation row is measured. There is no #VALUE! row: a direct argument that does not convert to a number is ignored, so MODE.SNGL("x",2,2) is 2 (FN-MODE-001).

How to implement

Numerical notes

There is no floating-point approximation here — no series, no argument reduction, no cancellation. Everything difficult about MODE.SNGL is discrete.

1. The equality predicate is the algorithm's only real decision. Multiplicity counting is parameterized by "same value", and a tolerant comparison is not an equivalence relation on the reals: if a ≈ b and b ≈ c under a fixed absolute-or-relative tolerance, a ≈ c can fail. An implementation that groups by a tolerant predicate therefore has to define what a group is — chaining, or clustering around a representative — and different definitions give different multiplicities on the same data. An implementation that groups by exact bits has no such problem and a different one: values that a user regards as equal are counted apart.
2. The tie-break falls out of the data structure, not out of a decision. A sort-then-scan implementation naturally reports the smallest tied value; a hash-map implementation reports whichever key the iteration order surfaces; a first-pass-wins scan reports the earliest in argument order. All three are O(n log n) or better and all three are defensible, and they disagree. This is the classic case where an implementation detail becomes an observable semantic, and the only correct response is to pin the rule deliberately and test it.
3. -0.0 and 0.0 compare equal under IEEE 754 == but have different bit patterns. A bitwise grouping separates them; a numeric grouping does not. The same question arises for values arriving from different cells with different formats.
4. Stability under argument regrouping. MODE.SNGL(A, B) and MODE.SNGL(B, A) should agree; under a first-encountered tie-break they need not. That is a cheap invariant to test and a cheap one to get wrong.

Rust code

7 mode_sngl "MODE.SNGL" (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 comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• MODE.MULT is the honest sibling: it returns the whole set A as a vertical array, which is what the mathematics actually produces. Where MODE.SNGL hides a tie, MODE.MULT exposes it. If you care whether your data has one mode or several, that is the function to use, and COUNT(MODE.MULT(…)) is the direct test.
• MEDIAN and AVERAGE are the other two classical centres. Only MODE.SNGL can fail to exist; only MODE.SNGL is unchanged by an arbitrary monotone relabelling of the values; only AVERAGE uses every observation.
• Confused with: MAX (largest value, not most frequent) and COUNTIF (how many times a given value occurs, which is m(v) for one v rather than the argmax over v).

Named variants

No named variants are recorded yet.