Distributions and outliers

01The problem

Harbourline's operations manager has a list of 247 delivered shipments on its busiest lane, Shanghai to Lagos (Apapa), and two questions:

"Which shipments were unusually slow? And is 'slow' something that happens randomly, or is there a pattern?"

Averages and standard deviations summarise data in one or two numbers, but they can't show you its shape. Two lanes with the same mean and standard deviation can look completely different, and the shape tells you what's actually going on: steady service with rare delays, or two different kinds of shipment mixed together.

02The concept

The distribution and the histogram

A distribution is how often each value occurs. A histogram shows it: the values are grouped into ranges (bins) along the bottom, and each bar's height is how many values fall in that range.

In Excel: select the column, then Insert → Charts → Histogram (Excel 2016 and later). Right-click the horizontal axis → Format Axis to set the bin width. In Google Sheets: Insert → Chart → Chart type: Histogram. Or count bins yourself with COUNTIFS(range, ">="&low, range, "<"&high).

Shapes to recognise

A bimodal histogram is a signal to split the data: it usually means two processes (sea and air, retail and wholesale) are being treated as one.

=SKEW(range) gives a number: about 0 is symmetric, positive is right-skewed, negative left-skewed. Values above about 1 are strongly skewed.

How unusual is a value? The z-score

A z-score says how many standard deviations a value is from the mean:

z = (value − mean) ÷ standard deviation

In Excel: =STANDARDIZE(value, mean, sd). A z-score of 0 is exactly average; +2 is two SDs above. For bell-shaped data, values beyond ±2 are unusual (about 1 in 20) and beyond ±3 very unusual (about 1 in 400). For skewed data, z-scores are a rougher guide, because the tail is longer on one side.

Flagging outliers: the IQR rule

A common, robust rule (the one behind box plots):

• Upper fence = Q3 + 1.5 × IQR
• Lower fence = Q1 − 1.5 × IQR

Values outside the fences are outliers. Because it's built from quartiles, extreme values don't distort the rule itself.

What to do with an outlier

An outlier is a question, not a mistake. Find out why before you act:

1. A data error (a typo, a test record, the wrong unit): fix it or remove it, and note what you did.
2. A real, rare event (a delayed vessel, a huge one-off order): keep it. It's often the most important thing in the data. Report it separately if it distorts an average.
3. A different kind of thing (an air shipment in a list of sea shipments): it belongs in a different group.

Never delete outliers just because they make a chart untidy.

03Example

Shanghai → Lagos (Apapa), delivered shipments, transit days:

• Mean 39.0 days, SD 4.7 days.
• The histogram has a tall block at 35–38 days (184 of 247 shipments) and then a long tail to the right, out to 53 days. Nothing arrives unusually early.

Z-scores make it concrete. The upper threshold of 2 SDs is 39.0 + 2 × 4.7 ≈ 48.4 days. 20 shipments are more than 2 SDs slow; none are more than 2 SDs fast.

That's the pattern the manager was asking about. On-time service on this lane is tight (35–38 days), and delays aren't random noise in both directions: they're a separate, one-sided problem. Something occasionally holds shipments up (port congestion, transhipment, customs), and it's worth investigating those 20 shipments by date and customer, rather than treating "39 ± 5 days" as the lane's normal behaviour.

04Walkthrough

1. Open Kolanut's orders.csv and add revenue = quantity × unit_price × (1 − discount_pct/100).
2. Insert a histogram of revenue. Describe its shape in one sentence. Check with =SKEW(revenue).
3. Calculate Q1, Q3, the IQR and the upper fence for revenue. Count the outliers with COUNTIF(revenue, ">"&upper_fence).
4. Sort by revenue, largest first, and look at the top outliers. Are they errors, or real large orders?
5. In the HR data, calculate the z-score of the highest salary (₦1,565,000) with STANDARDIZE.
6. In the logistics data, filter to delivered shipments on route 1, add transit_days, insert a histogram, and count shipments more than 2 SDs above the mean.

05Practice

06Challenge

07Check your understanding