Correlation

01The problem

Kolanut's sales director has noticed that order lines with a discount are much bigger than those without: about 17 packs against 11. Her proposal:

"Discounts make customers buy more. Let's give a 10% discount on everything."

It sounds like the data supports her. But is the discount causing the bigger orders, or are bigger orders getting the discount? The answer decides whether the new policy grows sales or just gives away margin on orders that would have happened anyway.

This lesson measures how strongly two things move together, and then, more importantly, how to think about what that relationship does and doesn't mean.

02The concept

Look first: the scatter chart

Put one variable on each axis and plot a dot for each row: Insert → Scatter in Excel or Google Sheets. In a few seconds you see whether the dots rise together, fall, or show no pattern; whether the relationship is a straight line or a curve; and whether a few outliers are doing all the work.

Measure: the correlation coefficient, r

=CORREL(range1, range2) gives Pearson's r, a number from −1 to +1:

These bands are rough guides, not rules. And r only measures straight-line relationships: a strong curve (sales rising then falling with price) can give an r near 0.

r²: how much is explained

Square r to get r² (=RSQ(range1, range2)): the share of the variation in one variable that's explained by a straight-line relationship with the other. r = 0.93 gives r² ≈ 0.87: containers explain about 87% of the variation in freight charges.

The traps

1. Correlation is not causation. Two things can move together because:
   • A causes B (more containers cause a higher charge);
   • B causes A (a supermarket opens more tills because it is busy, not the other way round);
   • something else causes both (hot weather raises both ice-cream sales and drowning numbers);
   • pure coincidence, especially when you test many pairs.
2. Outliers can create or hide a correlation. Check the scatter chart.
3. Mixing groups. Two groups with different levels can create a correlation that doesn't exist within either group (the same lesson as Simpson's paradox).
4. No correlation isn't "no relationship". It means no straight-line relationship.

Getting closer to cause

Data like Kolanut's is observational: nobody decided at random who gets a discount. The strongest way to establish cause is an experiment: give the discount to a random half of customers for a month and compare (lesson 9 shows how to test the difference). Without one, ask how the data was produced: who decided the discount, and why?

03Example

Three correlations from the practice data, with what each one means:

Back to the director. The 0.34 is real, but look at who gets discounts. Kolanut's discounts depend mostly on the customer's channel: 62% of wholesale lines are discounted, 27% of supermarket lines and 5% of kiosk lines. And wholesalers also order far more per line (19 packs, against 11 for supermarkets and under 4 for kiosks). Channel drives both the discount and the quantity: it's a third factor, called a confounder.

Check it by looking within one channel, so channel can't be the explanation. Among wholesale lines, discounted lines average 19.2 packs and undiscounted ones 18.7: almost no difference, and the correlation is about 0.02. The same holds inside the other two channels. Once you compare like with like, the "discount effect" disappears.

So a discount on everything would cost margin on every order, with no evidence it would grow volume. The honest recommendation: "Discounted lines are larger overall (r = 0.34), but only because wholesalers get most of the discounts and also place the biggest orders. Within each channel, discounted and full-price lines are the same size. To test whether a discount changes behaviour, offer it to a random half of comparable customers for a month and compare."

And the HR result deserves a sentence too: pay that doesn't rise with service is something HR would want to know about, especially alongside lesson 1's finding about who leaves.

04Walkthrough

1. Open the logistics shipments.csv. Insert a scatter chart of containers (x) against freight_charge (y). Describe what you see.
2. Calculate =CORREL(containers, freight_charge) and =RSQ(…).
3. Open Kolanut's orders.csv. Calculate CORREL(quantity, discount_pct). Compare the average quantity of discounted and undiscounted lines with AVERAGEIFS.
4. Open the HR employees.csv. Add years = (exit_date if there is one, otherwise 30 June 2026, minus hire_date) ÷ 365.25. Scatter years against monthly_salary, and calculate CORREL.
5. Add each order line's channel from customers.csv with XLOOKUP. Filter to Wholesale and calculate CORREL(quantity, discount_pct) again. What happened to the relationship?
6. Write the director a two-sentence reply about the discount proposal.

05Practice

06Challenge

07Check your understanding