Confident Cells

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.

Your turn

One customer took months to pay, which pulls the mean right up. In H1, find the median number of days customers took to pay.

ABCDEFGH
1CustomerDays to payTypical days
2Oakfield IT32
3Kestrel Cleaning20
4Redbrook Couriers28
5Northgate Supplies40
6Willow Training36
7Harbour Catering38
8Summit Software12
9Ashby Stationers9
10Brightway Print128

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

AB
1NameSalary (£k)
2Amira24
3Ben26
4Cara26
5Dev31
6Ewa90
  1. =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.
  2. =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.
  3. =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

  1. One customer took months to pay, which pulls the mean right up. In H1, find the median number of days customers took to pay.
  2. 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.
  3. These are the times from the staff 5k charity run, where a lower time is better. In H1, find the third-fastest time.
  4. 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.
  5. 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.