Rates, percentages and weighted averages

01The problem

Harbourline's quarterly board pack has a slide that reads:

"Air freight on-time delivery fell 13% this year. Average discount given: 2.7%."

Both numbers are wrong, and not because of a calculation slip. The on-time rate fell from 80.9% to 67.8%: that's 13.1 percentage points, which is a 16% fall. And the discount figure averages the discount percentage across order lines as if every line were the same size, when big orders get bigger discounts. Weighted properly, Kolanut gives away 3.4% of gross sales in discounts, a quarter more than the slide says.

Rates, percentages and averages of averages cause more wrong conclusions in business reporting than any formula error. The rules are simple once you've seen them.

02The concept

Percent change versus percentage points

When the thing you're measuring is itself a percentage (an on-time rate, a market share, a conversion rate), there are two ways to describe a change:

• Percentage points (pp): the simple difference. 80.9% → 67.8% is a fall of 13.1 pp.
• Percent change: the change relative to where you started. (67.8 − 80.9) ÷ 80.9 = −16.2%.

Both are correct; they answer different questions. The mistake is writing "fell 13%" when you mean 13 points. Always say which: "on-time delivery fell 13.1 percentage points, from 80.9% to 67.8%". Giving the start and end values removes all doubt.

Rates need their base

A rate is a count divided by a base: on-time shipments ÷ delivered shipments. Before comparing rates, check:

1. The base is right. Cancellations ÷ all bookings, not ÷ delivered shipments. On-time ÷ delivered, not ÷ all bookings (cancelled shipments can't be on time).
2. The bases are big enough. 2 of 3 is 67%, but you'd want far more than 3 before quoting it.
3. You show the counts next to the percentage: "67.8% (80 of 118)".

Weighted averages

A simple average treats every row equally. A weighted average gives each value a weight, such as its size:

weighted average = Σ(value × weight) ÷ Σ(weight)

In Excel: =SUMPRODUCT(values, weights) / SUM(weights).

Use a weighted average whenever the rows differ in size and the question is about the whole: the average discount on sales (weight by sales value), the average price per pack sold (weight by packs), the average salary across departments (weight by headcount). The simple average answers a different question: "what's the discount on a typical order line?"

Simpson's paradox

Sometimes a pattern that holds in every group reverses when the groups are combined, because the groups are different sizes. An illustration with made-up numbers:

Depot B is better on both kinds of delivery. But Depot A looks far worse overall, and B even better, simply because A handles mostly hard deliveries. Judge A on its total and you'd blame the wrong team. When groups differ in mix, compare like with like: break the total down by the thing that differs.

03Example

Air freight on-time rate by year, from Harbourline's delivered shipments (on time = transit days ≤ the route's target):

The honest sentence: "Air on-time delivery fell 13.1 percentage points, from 80.9% to 67.8% (a 16% fall). 2026 covers January to August only, with 118 shipments."

Kolanut's discounts. The simple average of discount_pct over all order lines is 2.66%. The discount actually given, as a share of gross sales value, weights each line by its value (quantity × unit_price):

=SUMPRODUCT(discount_pct, quantity * unit_price) / SUMPRODUCT(quantity, unit_price)

That's 3.38%. The difference is the finding: larger lines get larger discounts, so discounts cost more than the per-line average suggests. Finance needs the 3.38%.

04Walkthrough

1. In Kolanut's orders.csv, calculate the simple average of discount_pct, then the value-weighted average with SUMPRODUCT. Why are they different?
2. Calculate the simple average unit_price and the price per pack weighted by quantity. Which answers "what does a pack sell for on average?"
3. In the logistics data, build transit_days and on_time (transit ≤ target_transit_days) for delivered shipments, with mode and booking year from the lookups.
4. Make a pivot table: mode in rows, year in columns, average of on_time (TRUE/FALSE averages to a rate when you use =--on_time as a 1/0 column).
5. For air freight, write the change in percentage points and in percent.
6. Calculate the cancellation rate per year: cancelled ÷ all bookings. Check you used the right base.

05Practice

06Challenge

07Check your understanding