04-02: Frequency Distribution Builder¶
Build a complete grouped frequency distribution from raw data — classes, boundaries, midpoints, relative and cumulative frequency — then draw the histogram, frequency polygon, and ogive from it.
Chapters applied: 02-01 · 02-02
Difficulty: ⭐⭐ Beginner–Intermediate
The Data¶
data/service_times.csv — 120 customer service times in minutes, deliberately right-skewed (a long tail of a few very slow calls), so the shape discussion has something to say.
What You Produce¶
- A class design:
k, widthw, limits, boundaries, midpoints — each choice justified - A full frequency table:
f,f/n, cumulativef, cumulative % - Four charts: histogram, frequency polygon, ogive, boxplot
- A written shape description and the resulting recommendation for which centre and spread to report
Excel Route¶
Step 1 — Class design¶
=COUNT(A2:A121) ' n -> 120
=MIN(A2:A121) ' min
=MAX(A2:A121) ' max
=E3-E2 ' range R
=ROUNDUP(1+3.322*LOG10(E1), 0) ' Sturges' k
=ROUNDUP(E4/E5, 0) ' raw width, then round to a CLEAN number
Sturges gives k ≈ 8. Round the width up to a friendly 10 and start the first class at a round number at or below the minimum.
Step 2 — Build the table¶
Lay out lower in D, upper in E, then:
=(D2+E2)/2 ' F2 midpoint Xm
=D2-0.5 ' G2 lower boundary
=E2+0.5 ' H2 upper boundary
=COUNTIFS($A$2:$A$121,">="&D2, $A$2:$A$121,"<="&E2) ' I2 frequency f
=I2/SUM($I$2:$I$10) ' J2 relative frequency
=SUM($I$2:I2) ' K2 cumulative f
=SUM($I$2:I2)/SUM($I$2:$I$10) ' L2 cumulative %
Two checks before you go further: SUM(I:I) must equal n, and SUM(J:J) must equal 1.
One-shot alternative (Excel 365):
Step 3 — Histogram¶
Insert ▸ Clustered Column on Xm (or the class labels) against f, then right-click a bar ▸ Format Data Series ▸ Gap Width = 0% so the bars touch. That single setting is what turns a bar chart into a histogram.
Faster route: Data ▸ Data Analysis ▸ Histogram, Bin Range = the upper limits, tick Chart Output and Cumulative Percentage.
Step 4 — Frequency polygon¶
Insert ▸ Scatter with Straight Lines and Markers on midpoints vs. f. Add a midpoint one class below the first and one above the last, each with f = 0, so the polygon closes on the axis.
Step 5 — Ogive¶
Insert ▸ Scatter with Straight Lines and Markers on upper boundaries vs. cumulative f. Start with the lower boundary of the first class at cumulative frequency 0.
Warning
The polygon uses midpoints; the ogive uses boundaries. Swapping them is the most common mistake in this project.
Step 6 — Boxplot and shape¶
=MIN(A2:A121)
=QUARTILE.INC(A2:A121,1)
=MEDIAN(A2:A121)
=QUARTILE.INC(A2:A121,3)
=MAX(A2:A121)
=SKEW(A2:A121) ' > 0 confirms the right skew
=AVERAGE(A2:A121) & " vs " & MEDIAN(A2:A121) ' mean > median -> right-skewed
Insert ▸ Insert Statistic Chart ▸ Box and Whisker for the plot itself.
Step 7 — Read the ogive¶
Draw across from cumulative frequency n/2 = 60 to the curve and down to the axis: that is the graphical median. Compare it with MEDIAN().
R Route¶
Prints the class design and the full table, then writes four plots. See r/analysis.R.
Python Route¶
Same table and figures via pandas + matplotlib. See python/analysis.py.
Checkpoints¶
- Class width was rounded up, and the last class contains the maximum
- Classes are mutually exclusive, exhaustive, continuous, and equal-width
-
Σf = nandΣ(f/n) = 1 - Histogram bars touch; bar-chart-style gaps are gone
- Polygon plotted at midpoints; ogive plotted at boundaries
- The shape is described in words, and the choice of centre/spread follows from it
Extend It¶
- Rebuild the table with
k = 5and again withk = 20. Which one hides the shape, and which one shows noise? - Compute the grouped mean and standard deviation from the table (03-02) and compare with the exact values from the raw data — report the percentage error
- Read P25, P50, and P90 off the ogive, then check them against
PERCENTILE.INC - Split the data into two halves and overlay two frequency polygons on one chart