Lessons Lesson 22

MEDIAN and MODE

About this lesson

MEDIAN returns the middle value of a range after sorting. MODE returns the value that appears most often. One extreme number does not move either of them much.

By the end you can

  • find the middle value with MEDIAN and the commonest with MODE.
  • tell when AVERAGE is the wrong summary and what to report instead.

Practises MEDIAN MODE AVERAGE

By Ali, Founder, Excel Lessons

The idea

Three summaries, three questions. AVERAGE: if the total were shared out equally, how much would each get? MEDIAN: what does a typical one look like? MODE: which one comes up most? House prices, salaries and waiting times are reported as medians. One mansion, one director or one lost bag makes the average describe nobody.

The mistake with MODE is a list where nothing repeats. It returns #N/A, because there is no most common value. The mistake with MEDIAN is expecting it to change when the outlier gets bigger. It does not. 1450 and 14500 give the same median, and that is the whole idea.

The mistake to watch for

Reporting the average of a list with one extreme value. It describes nobody in the list. Six houses near 210 and one at 1450 average nearly 390. The median ignores how big the outlier is. The other mistake is expecting MEDIAN to react when the outlier grows. It does not, and that is its purpose.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In B10, type:

=AVERAGE(B2:B8)

That is the street's average price.

B10
Row A B C D E F G
1 House Price (£k) Size sold
2 No. 1 210 42
3 No. 3 225 39
4 No. 5 198 42
5 No. 7 1450 44
6 No. 9 230 41
7 No. 11 215 42
8 No. 13 205 39
9 43
10 Average
11 Median Most common
12 Gap

Every step

  1. 1

    In B10, type: =AVERAGE(B2:B8). That is the street's average price.

    Hint. One range.

  2. 2

    MEDIAN is the middle value once sorted. In B11, find the median price with MEDIAN.

    Hint. Same range, different function.

  3. 3

    Leaving out the mansion leaves six prices. Read =MEDIAN(B2:B4,B6:B8) and say what it returns.

    Hint. Halfway between the third and fourth of six.

  4. 4

    MODE returns the value that appears most. In D11, find the size sold most with MODE.

    Hint. D2 to D9.

  5. 5

    In B12, subtract the median from the average. Shape: =AVERAGE(range)-MEDIAN(range). That shows how far the average sits above the median.

    Hint. One minus the other.

  6. 6

    The mansion's price changes to 14500. Read =ROUND(AVERAGE(B2:B8),0) and say what it returns.

    Hint. Seven prices, one of them enormous.