Course / Data Analysis

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:

This is exactly what the ToolPak's Histogram tool counts, but FREQUENCY updates when the scores change.

Steps

  1. In F2, count the scores in B2:B31 into the bins listed in E2:E6: =FREQUENCY(B2:B31, E2:E6). Five bins give six counts, spilling down to F7.

  2. 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".

  3. In G2, turn each count into a share of the class: =F2/$F$9, then fill it down to G7. The cells are already formatted as percentages.

  4. Now draw it. In H2, repeat a block character once per student with =REPT("█", F2), and fill it down to H7.

Remember

In the function reference: FREQUENCY, REPT, SUM

Previous: Forecasting a trend with TREND · Next: Descriptive statistics: the summary table