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
data_array: The values to count.bins_array: The upper limit of each bin, e.g. 10, 20, 30.
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
Scores into bins
A B C D E 1 Score Up to 2 72 50 1 3 85 70 3 4 64 90 3 5 91 1 6 47 7 77 8 69 9 58 In E2:
=FREQUENCY(A2:A9, C2:C4)→ 1; 3; 3; 1Up to 50, 51–70, 71–90, and the extra count for over 90.
Every value is counted once
A B C D E 1 Score Up to 2 72 50 8 3 85 70 4 64 90 5 91 6 47 7 77 8 69 9 58 In E2:
=SUM(FREQUENCY(A2:A9, C2:C4))→ 8Just the values above the top bin
A B C D E 1 Score Up to 2 72 50 1 3 85 70 4 64 90 5 91 6 47 7 77 8 69 9 58 In E2:
=INDEX(FREQUENCY(A2:A9, C2:C4), 4)→ 1
Common mistakes
- Forgetting the extra count. Three bins give four counts. Leave room for the last one, or values above the top bin seem to vanish.
- Bins as ranges like "51–60". Give each bin as a single number, its upper limit. Put labels like "51–60" in the next column for people to read.
- Limits that belong to the wrong bin. A value equal to a limit counts in that bin: with a limit of 50, a score of 50 is in the "up to 50" bin.
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.
- Czech: ČETNOSTI
- Danish: FREKVENS
- Dutch: INTERVAL
- Finnish: TAAJUUS
- French: FREQUENCE
- German: HÄUFIGKEIT
- Hungarian: GYAKORISÁG
- Italian: FREQUENZA
- Norwegian: FREKVENS
- Polish: CZĘSTOŚĆ
- Portuguese (Portugal): FREQUÊNCIA
- Russian: ЧАСТОТА
- Spanish: FRECUENCIA
- Swedish: FREKVENS
- Turkish: SIKLIK
Try FREQUENCY in the playground
Practise it
Related functions
- COUNTIFS: Counts the rows that meet every one of several conditions.
- COUNTIF: Counts the cells that meet a condition.
- PERCENTILE: The value below which a given percentage of the data falls.
- QUARTILE: A quartile of the data (0 = min, 2 = median, 4 = max).