Data Lab · Unit 4 of 8
Unit 4: Summaries that tell the truth
Mean versus median, percentages and percent change, spread, and grouping data with COUNTIF, SUMIF and pivot tables.
Opens the first lesson of this unit in Data Lab. Every lesson is open from the start.
Lesson 1: Mean versus median
Goal: Choose between the mean and the median and explain why.
The mean is what most people call the average: add everything up and divide by how many there are. The median is the middle value when everything is sorted. With an even number of values, it is the mean of the two middle ones.
Worked example: finding both
Hourly pay at a small cafe: $12, $13, $13, $14, $15, $16, $60 (the owner).
- Mean: total $143 ÷ 7, about $20.4 an hour.
- Median: sorted, the middle (4th of 7) value is $14.
- Six of the seven people earn less than the mean. The median describes a typical worker far better.
When a few values are much bigger or smaller than the rest, the data is skewed, and the mean gets pulled toward the extremes. The median barely moves. That is why house prices and incomes are usually reported as medians.
Hands-on lab in the room: Mean versus median. Drag the owner's pay up and down and watch what happens to the mean and the median.
Use the mean when values are fairly balanced, like test scores in a class without extreme outliers. Use the median when there are big outliers or a long tail. When in doubt, report both.
- Mean
- The sum of the values divided by how many there are.
- Median
- The middle value when the values are sorted.
- Skewed data
- Data where a few extreme values sit far to one side, pulling the mean toward them.
Remember: Big outliers? Use the median. Balanced data? The mean is fine. Unsure? Report both.
Lesson 2: Percentages and percent change
Goal: Calculate percentages and percent change, and avoid the classic mistakes.
A percentage is a part out of 100: part ÷ whole × 100. If 18 of 24 students passed, 18 ÷ 24 = 0.75, so 75% passed.
Percent change compares a new value with an old one:
percent change = (new − old) ÷ old × 100
Worked example: sales growth
- Last month 400 cups, this month 500.
- (500 − 400) ÷ 400 = 100 ÷ 400 = 0.25, so sales rose 25%.
- If sales go from 500 back to 400: (400 − 500) ÷ 500 = −0.2, a 20% fall. The same 100 cups is a different percentage because the starting point changed.
Two classic traps:
- Percent versus percentage points. If a pass rate goes from 40% to 50%, it rose by 10 percentage points, but by 25% in relative terms (10 ÷ 40). Headlines mix these up.
- Big percentages of tiny numbers. "Sales up 300%!" might mean going from 1 sale to 4. Always ask: a percentage of what?
In a spreadsheet, percent change is =(C2-B2)/B2, then format the cell as a percentage.
- Percent change
- (new − old) ÷ old × 100: how much something grew or shrank relative to where it started.
- Percentage point
- The plain difference between two percentages, such as 40% to 50% being 10 points.
Remember: Percent change divides by the OLD value. And always ask: a percentage of what?
Lesson 3: Spread: how much values vary
Goal: Describe spread with the range and understand why it matters.
Two groups can have the same average and look nothing alike. Spread tells you how much the values vary.
Worked example: same average, different story
- Bus A arrival delays (minutes): 4, 5, 5, 6, 5. Mean 5.
- Bus B arrival delays: 0, 0, 1, 2, 22. Mean 5.
- Same mean, but Bus A is reliably 5 minutes late. Bus B is usually on time and occasionally terribly late.
If you have to be somewhere on time, Bus A's low spread may be the better choice, even though the averages match.
Common ways to describe spread:
- Range: maximum minus minimum. Easy, but one extreme value can dominate it. Bus A: 6 − 4 = 2. Bus B: 22 − 0 = 22.
- Interquartile range (IQR): the spread of the middle half of the data, which ignores the extremes.
- Standard deviation: roughly, a typical distance of values from the mean. Spreadsheets calculate it with
STDEV. Bigger means more spread out.
When you report an average, adding the spread makes it far more honest: "Average delivery 32 minutes, but 1 in 10 took over an hour" tells customers what they actually need to know.
- Range
- The largest value minus the smallest.
- Standard deviation
- A measure of how spread out values are around the mean. Larger means more variation.
Remember: An average without spread hides half the story. Report the range or how often things go badly.
Lesson 4: Grouping: COUNTIF, SUMIF and pivot tables
Goal: Summarize data by group with conditional functions and pivot tables.
Many questions are about groups: sales by region, average score by class, orders by month. Spreadsheets have functions that only include rows meeting a condition:
| Function | What it does | Example |
|---|---|---|
COUNTIF(range, condition) | counts cells that meet the condition | =COUNTIF(B2:B30,"North") |
SUMIF(range, condition, sum_range) | adds the sum_range values where the range meets the condition | =SUMIF(B2:B30,"North",C2:C30) |
AVERAGEIF(range, condition, avg_range) | averages where the condition is met | =AVERAGEIF(B2:B30,"North",C2:C30) |
Conditions can be text ("North") or comparisons in quotes: ">100", "<=5", "<>Cancelled" (not equal to).
Hands-on lab in the room: Spreadsheet lab. Count and total the orders for each region.
For many groups at once, use a pivot table (Excel and Google Sheets both have them, usually under Insert). You choose a column for the rows (region), and a value to summarize (sum of amount), and it builds the grouped summary in seconds, with no formulas to write. It is the single most useful tool for exploring a big table.
- COUNTIF / SUMIF
- Functions that count or add only the rows meeting a condition.
- Pivot table
- A tool that groups rows by one or more columns and summarizes each group, such as totals by region.
Remember: Questions with "by" or "per" in them (sales by region, score per class) are grouping questions.
Practise it in Data Lab
Each lesson ends with two or three questions that check you can use the idea, not just recognise it. Wrong answers explain why and come back in Review. The unit ends with a 6-question quiz; pass it with 80% or more to master the unit. Hands-on lab: Mean versus median and Spreadsheet lab.
Every lesson is free: no ads, no lives and no paywall.