Excel functions / Statistics

FREQUENCY function

Counts how many values fall into each bin, for a histogram. Spills one more count than there are bins: the last counts values above the top bin.

Syntax

=FREQUENCY(data_array, bins_array)

Arguments

When to use it

FREQUENCY counts how many values fall into each bin, which is what a histogram shows. You give the data and the upper limit of each bin, and it spills one count per bin down a column.

Each count includes values above the previous limit, up to and including its own. There is always one extra count at the end, for values above the last limit.

Examples

  1. Scores into bins

    ABCDE
    1ScoreUp to
    272501
    385703
    464903
    5911
    647
    777
    869
    958

    In E2: =FREQUENCY(A2:A9, C2:C4) → 1; 3; 3; 1

    Up to 50, 51–70, 71–90, and the extra count for over 90.

    Try this example

  2. Every value is counted once

    ABCDE
    1ScoreUp to
    272508
    38570
    46490
    591
    647
    777
    869
    958

    In E2: =SUM(FREQUENCY(A2:A9, C2:C4)) → 8

    Try this example

  3. Just the values above the top bin

    ABCDE
    1ScoreUp to
    272501
    38570
    46490
    591
    647
    777
    869
    958

    In E2: =INDEX(FREQUENCY(A2:A9, C2:C4), 4) → 1

    Try this example

Common mistakes

For a quick in-cell chart, put =REPT("█", E2) next to each count.

FREQUENCY 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 FREQUENCY in the playground

Practise it

Related functions