Histograms with FREQUENCY
Count exam scores into bins with one formula, turn the counts into percentages, and draw the histogram in the cells. Intermediate · 6 min · FREQUENCY, Histogram, Bins, REPT
A histogram shows how values are spread out by counting how many fall into each range, or bin. You describe the bins by their upper limits: 50, 60, 70 and so on.
=FREQUENCY(data, bins) counts the data into those bins in one go and spills the counts down a column:
- The first count is every value up to and including the first limit.
- Each next count is the values above the previous limit, up to and including the next one.
- There is always one extra count at the end, for values above the last limit.
This is exactly what the ToolPak's Histogram tool counts, but FREQUENCY updates when the scores change.
Steps
In
F2, count the scores inB2:B31into the bins listed inE2:E6:=FREQUENCY(B2:B31, E2:E6). Five bins give six counts, spilling down toF7.Every score should land in exactly one bin. In
F9, add up the counts with=SUM(F2#). The#means "the whole spilled range starting at F2".In
G2, turn each count into a share of the class:=F2/$F$9, then fill it down toG7. The cells are already formatted as percentages.Now draw it. In
H2, repeat a block character once per student with=REPT("█", F2), and fill it down toH7.
Remember
=FREQUENCY(data, bins)spills one count per bin, plus one more for values above the last bin.- Each bin counts values above the previous limit, up to and including its own.
- Refer to the spilled counts as
F2#, so totals keep up when bins change. =REPT("█", n)draws quick in-cell bars. The ToolPak's Histogram tool gives the same counts, but they don't update.
In the function reference: FREQUENCY, REPT, SUM
Previous: Forecasting a trend with TREND · Next: Descriptive statistics: the summary table