"Comparing groups: is the difference real?"

01The problem

In January 2026, Kolanut raised its prices by about 9% across its range. Six months later the commercial director wants to know:

"Did the price rise make customers buy less? Average quantity per order line dropped from 14.0 packs to 13.7. Should we roll the prices back?"

Meanwhile at Harbourline, air freight's on-time rate has fallen from 80.9% to 67.8%, and the operations director asks whether that's a real problem or a bad run of luck.

In both cases the numbers are different. The question is whether the difference is bigger than you'd expect from chance alone. That's what a hypothesis test answers, and it's the step between "the numbers moved" and "something changed".

02The concept

The logic of a test

1. Start from the null hypothesis: there is no real difference; any gap is just sampling variation.
2. Calculate how surprising your data would be if the null were true. That's the p-value: the probability of seeing a difference at least this big by chance alone.
3. If the p-value is small, chance is an unlikely explanation, so you conclude there's a real difference. The usual threshold is 0.05: below it, the result is called statistically significant.

A p-value of 0.23 means: if there were no real effect, you'd see a gap this big about 23% of the time. That's common, so it's not evidence of an effect. A p-value of 0.01 means you'd see it only 1% of the time by chance, so something real is probably going on.

Comparing two averages: the t-test

=T.TEST(range1, range2, 2, 3)

• The 2 means two-tailed: you're testing for a difference in either direction. Use this unless you decided in advance that only one direction matters.
• The 3 means two samples with possibly different spreads (Welch's test). It's the safe default.

It returns the p-value directly.

Comparing two rates: the two-proportion test

For rates p₁ (from n₁) and p₂ (from n₂), with the pooled rate p = all "yes" ÷ all observations:

z = (p₁ − p₂) ÷ √( p × (1 − p) × (1/n₁ + 1/n₂) )

and the two-tailed p-value is =2 * (1 - NORM.S.DIST(ABS(z), TRUE)).

What a test can't tell you

• "Not significant" doesn't mean "no difference". It means you can't tell the difference from noise with this much data. A real but small effect needs a bigger sample.
• Significant doesn't mean important. With thousands of rows, a difference of 0.1 packs can be "significant" and still irrelevant. Always report the size of the difference, ideally with a confidence interval, not just the p-value.
• A test doesn't fix a biased comparison. If the groups differ in other ways (as discounted and full-price lines differ by channel in lesson 6), a significant result still doesn't prove cause.
• Test many things and some will be significant by luck. At 0.05, about 1 in 20 tests of differences that don't exist will come out "significant". Decide what you're testing before you look.

03Example

Did the price rise reduce order size? Compare the six months before (July–December 2025) with the six months after (January–June 2026):

=T.TEST(before_quantities, after_quantities, 2, 3)     0.23

p = 0.23: a gap of this size would turn up by chance about a quarter of the time. No evidence that the price rise reduced order size. Comparing like with like makes the point stronger: January–June 2025 averaged 13.56 packs, slightly below January–June 2026 (p = 0.66). The answer for the director: "There's no sign customers are buying less per order since the price rise. Average quantity is within normal variation (p = 0.23), and slightly above the same months last year. Rolling prices back would give away about 9% of revenue for no evidence of lost volume."

Is air freight's on-time fall real? 127 of 157 on time in 2025 (80.9%) against 80 of 118 in 2026 (67.8%). The pooled rate is 207 ÷ 275 = 75.3%, so:

z = (0.809 − 0.678) ÷ √(0.753 × 0.247 × (1/157 + 1/118)) ≈ 2.49, and p ≈ 0.013.

That's well below 0.05: the fall is very unlikely to be chance. "Air on-time delivery has genuinely fallen, by 13 points, and it's worth finding out why: which routes, and since when."

04Walkthrough

1. In Kolanut's orders.csv, filter order_date to July–December 2025 and copy the quantity values to a new sheet, column A. Do the same for January–June 2026 into column B.
2. Calculate each column's mean, then =T.TEST(A:A, B:B, 2, 3).
3. Repeat with January–June 2025 against January–June 2026.
4. In the logistics data, count on-time and total delivered air shipments by booking year (a pivot table from lesson 5).
5. Calculate the two-proportion z and its p-value with NORM.S.DIST.
6. Write one sentence for each director: the size of the difference, the p-value, and what to do.

05Practice

06Challenge

07Check your understanding