Mean, median and mode: practice exercises
MEDIAN, MODE, SMALL, leaving out the extremes. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
One customer took months to pay, which pulls the mean right up. In H1, find the median number of days customers took to pay.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Customer | Days to pay | Typical days | |||||
| 2 | Oakfield IT | 32 | ||||||
| 3 | Kestrel Cleaning | 20 | ||||||
| 4 | Redbrook Couriers | 28 | ||||||
| 5 | Northgate Supplies | 40 | ||||||
| 6 | Willow Training | 36 | ||||||
| 7 | Harbour Catering | 38 | ||||||
| 8 | Summit Software | 12 | ||||||
| 9 | Ashby Stationers | 9 | ||||||
| 10 | Brightway Print | 128 |
How it works
The mean (AVERAGE) adds everything up and shares it out equally. The median (MEDIAN) is the middle value once they're in order, so one huge value can't drag it up. The mode (MODE) is the value that comes up most often.
SMALL(range, k) gives the k-th lowest value, as LARGE gives the k-th highest.
=MEDIAN(B2:B8) =MODE(B2:B8) =SMALL(B2:B8,2) =(SUM(B2:B6)-MAX(B2:B6)-MIN(B2:B6))/3
Three kinds of average
- Mean,
AVERAGE(range): add them all up and divide by how many there are. - Median,
MEDIAN(range): put them in order and take the middle one. With an even number of values, it takes the mean of the middle two. - Mode,
MODE(range): the value that comes up most often. Good for sizes and choices, where "typical" means "most popular".
Worked example
| A | B | |
|---|---|---|
| 1 | Name | Salary (£k) |
| 2 | Amira | 24 |
| 3 | Ben | 26 |
| 4 | Cara | 26 |
| 5 | Dev | 31 |
| 6 | Ewa | 90 |
=AVERAGE(B2:B6)in D2. The mean salary is 197 ÷ 5 = 39.4. Four of the five earn less than that, because Ewa's 90 pulls it up.=MEDIAN(B2:B6)in D3. In order, the middle salary is 26. Ewa's salary makes no difference to it, which is why median pay is quoted in reports.=MODE(B2:B6)in D4. 26 comes up twice and nothing else does, so the mode is 26.
SMALL, and leaving out the extremes
SMALL(range, 1) is the lowest value, the same as MIN. SMALL(range, 2) is the second lowest, and so on.
Judges' scores often leave out the highest and lowest: (SUM(range)-MAX(range)-MIN(range)) divided by how many are left.
What you'll practise
- One customer took months to pay, which pulls the mean right up. In H1, find the median number of days customers took to pay.
- The company is ordering safety boots, and wants extra stock of the most common size. In H1, find the shoe size that comes up most often.
- These are the times from the staff 5k charity run, where a lower time is better. In H1, find the third-fastest time.
- The manager wants the three lowest stock levels, to reorder first. In H2, find the stock level for the place in G2 (1 for the lowest). Your formula will be copied down to H4.
- Five judges score each candidate out of 10. To stop one very high or very low score deciding things, the highest and lowest are left out. In G2, work out the mean of the other three scores.