Charts & Data Visualization
Stem-and-Leaf Plot Formula: Tens Stem Extraction, Leaf Digit & Bin Frequency
Construct John Tukey’s Exploratory Data Analysis (EDA) Stem-and-Leaf display in a spreadsheet by splitting observations into integer Stems INT(x / 10), Trailing Leaf Digits MOD(x, 10), and Per-Stem Bin Frequencies COUNTIFS.
Preserve exact raw observations inside a histogram-shaped stem-and-leaf table!
Introduced by statistician John W. Tukey (1977, Exploratory Data Analysis), a Stem-and-Leaf Plot acts simultaneously as a frequency histogram and an exact numerical table: splitting a two-digit exam score such as 78 into Leading Tens Stem INT(78 / 10) = 7 and Trailing Units Leaf MOD(78, 10) = 8 preserves every raw score while visually revealing distribution shape, skewness, gaps, and outliers!
For general scale unit S (such as S = 10 for exam scores 0–100, S = 100 for millisecond latencies, or S = 1 for one-decimal readings multiplied by 10), each observation x splits cleanly via Stem = INT(x / S) and Leaf = ROUND(MOD(x, S) / (S / 10), 0), while COUNTIFS(B2:B9, ">=" & (E2 * 10), B2:B9, "<" & ((E2 + 1) * 10)) tallies exact observations inside Stem E2!
| Tens Stem Bin (Scale S = 10) | Lower–Upper Score Window | Sorted Leaf Digits | Stem Frequency Count |
|---|---|---|---|
| Stem 6 (60–69) | >= 60 and < 70 | 4, 8 | 2 scores |
| Stem 7 (70–79) | >= 70 and < 80 | 2, 5, 5, 9 | 4 scores |
| Stem 8 (80–89) | >= 80 and < 90 | 3, 7 | 2 scores |
Per-Stem Bin Observation Frequency COUNTIFS
Counts how many raw scores in B2:B9 belong to non-negative tens stem E2 (covering [10 * E2, 10 * E2 + 9.999...]).
=IF(E2 < 0, "Requires Non-Negative Tens Stem", COUNTIFS(B2:B9, ">=" & (E2 * 10), B2:B9, "<" & ((E2 + 1) * 10)))Individual Observation Stem INT(x / 10) & Units Leaf MOD(x, 10)
Extracts the integer tens stem and single-digit units leaf for an individual non-negative score in B5.
=IF(B5 < 0, "Requires Non-Negative Score", INT(B5 / 10) * 10 + MOD(B5, 10) - INT(B5 / 10) * 10)Try the example
Use the original values or change them to test your understanding.