FREQUENCY

What it does

FREQUENCY(data_array, bins_array)

FREQUENCY(data_array, bins_array) counts how many values of data_array fall into each interval defined by bins_array, and returns those counts as a vertical array with one more element than bins_array has.

Given m bin boundaries b₁ ≤ b₂ ≤ … ≤ b_m, the returned counts c₁ … c_{m+1} are

c₁      = #{ v : v ≤ b₁ }
c_k     = #{ v : b_{k−1} < v ≤ b_k }        for 2 ≤ k ≤ m
c_{m+1} = #{ v : v > b_m }

Three properties define the function and all three are documented.

The intervals are half-open, closed on the right. A value exactly equal to a boundary falls into the interval ending at that boundary, not the one starting there. This is the opposite convention from most histogram tooling — NumPy, R's hist, and most plotting libraries default to left-closed, right-open bins — and it is the single most common source of off-by-one disagreement when a spreadsheet histogram is reproduced elsewhere. Microsoft's own worked description is the "count of test scores in ranges of scores" framing; the boundary rule follows from the extra-element description quoted below.

The result has one more element than there are bins. Microsoft states this directly, and explains the extra element: it "returns the count of any values above the highest interval". The m boundaries cut the real line into m + 1 pieces, and the function returns all m + 1 counts. Entering FREQUENCY over the same number of cells as bins silently discards the overflow count.

The result is a vertical array. Not a row, not a scalar, whatever the shapes of the inputs. Microsoft's wording — "returns a vertical array of numbers" — is a shape guarantee, and it means an implementation cannot echo the orientation of bins_array.

Counts are returned as numbers; the sum of all m + 1 counts equals the number of admitted values in data_array, which is not the same as the number of cells in it — see the ignore rule under Arguments.

Arguments

FREQUENCY ignores blank cells and text. That is Microsoft's sentence, verbatim in substance, and it is the scan policy for both arguments. Note what it does not say: it says nothing about logical values, and nothing about text that looks like a number. Those are unsettled here and are on the probe list.

Both arguments are consumed by scanning. This is not a lift kernel and there is no elementwise broadcast; an array argument is data, not a call to repeat the function.

Microsoft's remark that FREQUENCY "must be entered as an array formula" is a pre-dynamic-array statement. On a build with dynamic arrays the result spills from a single cell. The Handbook has not checked how the two entry modes differ on this function, and the difference is exactly the kind that changes what a reader sees without changing what the function computes.

Try it

Quirks

The bin convention

Worth isolating, because it is where readers get the wrong answer while believing they got the right one.

Consequences:

• A data value equal to b₁ is counted in the first bin.
• A data value equal to b_m is counted in bin m, not in the overflow bin.
• Duplicate boundaries produce an empty interval between them, whose count is necessarily zero.
• Reversing a "greater than or equal" intuition by shifting boundaries down by one unit works for integers and fails for continuous data. There is no option to switch the convention.

Result and edge cases

Returns Array — a vertical array of numbers, of length LEN(bins_array) + 1.

• No admitted values in data_array: documented as an array of zeros. The array still has one element more than the bin count.
• No admitted values in bins_array: documented as "the number of elements in data_array", which is the degenerate m = 0 case — a single-element array holding the total count. Note the documented wording says elements of data_array, while the same page says text and blanks are ignored; whether an ignored cell is an "element" for this rule is not stated.
• Both empty: falls under both rules at once, which the documentation does not disambiguate.
• Duplicate boundaries give a zero count for the collapsed interval.
• The counts are exact integers held as doubles, so there is no accuracy question about the counts themselves. The only comparison performed is value ≤ boundary, which is exact IEEE comparison — and that is where the numerical content of this function actually lives; see Numerical notes.
• Errors inside the scanned ranges propagate under the shared coercion discipline; see Coercion and lifting.

Errors

Microsoft's documentation for this function lists no error conditions at all. The Remarks describe entry mode, the extra element, and the ignore rule; there is no error table.

Neither has a documented counterpart, and neither has been compared against Excel by the Handbook. The #REF! is the more surprising of the two: #REF! is the invalid-reference error, and nothing in the documented behaviour of FREQUENCY suggests a route to it from well-formed array literals.

How to implement

Numerical notes

FREQUENCY has no arithmetic in it, and that makes its numerical behaviour easier to state and no less consequential.

The whole function is a comparison predicate. Every count depends only on value ≤ boundary evaluated in IEEE double precision. Two things follow. First, values that a user thinks are equal to a boundary may not be: 0.1 + 0.2 is not 0.3, and a data point produced by arithmetic lands on the wrong side of a boundary typed as a literal. This is not a defect in FREQUENCY; it is the boundary convention meeting binary floating point, and it is why bin edges chosen midway between achievable data values are the robust choice. Second, the comparison is exact, so the function is deterministic and reproducible in a way almost no other member of the Statistical category is — there is no summation order, no cancellation, no transcendental.

Counts are exact. Integers up to 2⁵³ are exactly representable, and no spreadsheet range reaches that, so the returned counts are exact integers with no accumulation error. Any disagreement between two implementations of FREQUENCY is therefore a semantic disagreement — about the boundary convention, the ignore rule, or the sortedness requirement — never a rounding one. That is unusual in this category and it makes disagreements here unusually diagnostic.

Rust code

54 frequency "FREQUENCY" (2, 2) [R "data_array", R "bins_array"] |s, a, sv| {
    let data = list(s, a, 0, sv)?;
    let bins = list(s, a, 1, sv)?;
    out_matrix(s, &frequency_counts(&data, &bins))
}

/// FREQUENCY's counts, one per bin in the bins' order and one more for the numbers above every
/// bin: a bin counts the numbers above the next smaller bin and at most itself; a repeated bin
/// counts in its first place and 0 in the others.
fn frequency_counts(data: &[f64], bins: &[f64]) -> Vec<Vec<f64>> {
    let mut counts = vec![0.0; bins.len() + 1];
    for value in data {
        let mut best: Option<usize> = None;
        for (index, bin) in bins.iter().enumerate() {
            if value <= bin && best.is_none_or(|b| *bin < bins[b]) {
                best = Some(index);
            }
        }
        counts[best.unwrap_or(bins.len())] += 1.0;
    }
    counts.into_iter().map(|c| vec![c]).collect()
}

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

• COUNTIF / COUNTIFS — the per-bin alternative. One COUNTIFS per interval reproduces FREQUENCY, and doing so makes the boundary convention explicit instead of implicit, which is why it is the usual debugging move when a histogram looks wrong by one.
• COUNTIF with ">" and "<=" — the direct spelling of a single half-open bin.
• MATCH with match type 1 — the same "largest boundary not exceeding" search that a sorted-bin implementation performs internally, exposed as a lookup.
• HISTOGRAM (Analysis ToolPak) and Excel's chart histogram — separate machinery with its own binning rules; agreement with FREQUENCY is not to be assumed.
• GROUPBY / PIVOTBY — the modern aggregation route, which supersedes FREQUENCY for most reporting uses without superseding it as a function.

Named variants

No named variants are recorded yet.