"Spread: range, IQR and standard deviation"

01The problem

Harbourline Freight's customers keep asking the same question: "How long will my shipment take?" The operations manager's answer is the average: "Sea freight takes about 27 days."

A customer planning a factory production run doesn't just need the average. They need to know how reliable it is. If almost every shipment arrives in 25–29 days, they can plan around 27. If some take 10 days and others 50, the average is nearly useless and they need to hold more stock.

Two datasets can have exactly the same average and behave completely differently. Spread (or variability) is the second number you report alongside every average.

02The concept

Range: the simplest spread

=MAX(range) - MIN(range). Easy to explain, but it depends entirely on the two most extreme values, so one unusual shipment can double it.

Quartiles and the interquartile range (IQR)

Sort the data and cut it into four equal parts:

• Q1 (25th percentile): a quarter of values are below it.
• Q2 = the median.
• Q3 (75th percentile): three quarters are below it.

The IQR = Q3 − Q1 is the range of the middle half of the data. Extremes don't affect it, which makes it the natural partner of the median.

=QUARTILE.INC(range, 1)      Q1
=QUARTILE.INC(range, 3)      Q3
=PERCENTILE.INC(range, 0.9)  the 90th percentile

Percentiles answer service-level questions directly: "90% of sea shipments arrive within X days" is a promise a customer can plan around.

Standard deviation: the typical distance from the mean

The standard deviation (SD) measures how far values typically are from the mean, in the same units as the data. Roughly:

1. Find each value's distance from the mean.
2. Square the distances (so negatives don't cancel positives), and average them: that's the variance.
3. Take the square root to get back to the original units: the standard deviation.

=STDEV.S(range)    a sample: divides by n − 1
=STDEV.P(range)    a whole population: divides by n

Which one? Use STDEV.S when your data is a sample and you want to describe the wider population, which is almost always the case (this month's shipments as a guide to future shipments). Use STDEV.P only when the data is the whole population and you only care about it. With a few hundred rows the two are nearly identical; with 10 rows they differ noticeably.

The SD goes with the mean; the IQR goes with the median. On skewed data, report the median and IQR.

Comparing spread fairly: the coefficient of variation

Managers' salaries vary by about ₦121,000 and juniors' by about ₦62,000. Are managers' salaries more variable? Not relative to their size. The coefficient of variation (CV) puts spread on a common scale:

CV = standard deviation ÷ mean × 100%

Junior salaries: CV ≈ 22%. Manager salaries: CV ≈ 9%. Junior pay is actually twice as variable relative to its level.

03Example

Sea freight transit times, in Harbourline's logistics data. Add a column transit_days = delivery_date − ship_date to shipments.csv, look up each shipment's mode from routes.csv with XLOOKUP, and filter to Delivered Sea shipments.

Across all sea routes, the spread is enormous (a CV of 47%), because it mixes 4-day coastal hops with 40-day voyages from China. On a single route, shipments are far more predictable: Shanghai to Apapa takes 39 days, give or take about 5.

So the useful answer to the customer isn't "27 days". It's per route: "From Shanghai, plan for 39 days; most shipments arrive within about 5 days either side."

04Walkthrough

1. Open Kolanut's orders.csv. Calculate the mean and standard deviation of quantity with AVERAGE and STDEV.S. Then STDEV.P. How different are they with 4,266 rows?
2. Calculate Q1, Q3 and the IQR of quantity with QUARTILE.INC. The middle half of order lines are between which quantities?
3. Open the HR employees.csv. Calculate the median and IQR of monthly_salary, then the mean and SD. Which pair would you report, and why?
4. Calculate the CV for Junior and for Manager salaries: =STDEV.S(IF(D2:D81="Junior", G2:G81)) / AVERAGEIFS(G2:G81, D2:D81, "Junior").
5. Open the logistics shipments.csv and routes.csv. Build transit_days and mode, filter to delivered sea shipments, and calculate the SD. Then do the same for route 1 only.
6. Write the sentence you'd give a customer shipping from Shanghai.

05Practice

06Challenge

07Check your understanding