Course / Statistics & Ranking

Percentiles and quartiles

Find the time most deliveries beat, measure the middle half of your data, and flag the slow ones. Intermediate · 5 min · PERCENTILE, QUARTILE, Interquartile range, IF

An average can't tell you how many people waited a long time. Percentiles can. The 90th percentile is the value that 90% of the data sits at or below.

• =PERCENTILE(range, k) takes k as a fraction, so the 90th percentile is 0.9
• =QUARTILE(range, q) is a shortcut for the quarter marks: 1 for the 25th percentile, 2 for the median and 3 for the 75th

The gap between quartile 1 and quartile 3 is the interquartile range (IQR): how spread out the middle half of your data is.

Steps

1. Your bike courier co-op wants to promise a delivery time it can actually keep. In F2, find the time that 90% of the deliveries in B2:B17 came in at or under: =PERCENTILE(B2:B17, 0.9)

2. Quartiles cut the sorted times into four equal groups. In F3, find quartile 1 with =QUARTILE(B2:B17, 1), and in F4 find quartile 3.

3. In F5, work out the interquartile range: quartile 3 minus quartile 1.

4. Now flag the slow trips. In C2, show "Slow" when the time in B2 is above the 90th percentile in F2, and leave the cell blank otherwise: =IF(B2>$F$2, "Slow", ""). Fill it down to C17.

Remember

• PERCENTILE(range, 0.9) gives the value that 90% of the data is at or below.
• QUARTILE(range, 1) and QUARTILE(range, 3) mark the 25th and 75th percentiles.
• The IQR (Q3 − Q1) measures the spread of the middle half, ignoring the extremes.
• Compare each row with a locked percentile cell to flag the outliers.

Previous: Mean, median and mode · Next: Standard deviation and z-scores