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

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.

Where this comes up again

FILTER Dynamic Arrays
SUMPRODUCT: Weighted Totals and Conditions Summaries and Reports
Project: A Grade Book Projects

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, the average house price on the street, with AVERAGE.

To begin, type it exactly:

=AVERAGE(B2:B8)

B10

Row	A	B	D
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

In B10, the average house price on the street, with AVERAGE. To begin, type it exactly: =AVERAGE(B2:B8).

Hint. One range.
2

In B11, the median price: the middle value when the seven are put in order. Use MEDIAN.

Hint. Same range, different function.
3

Leave the mansion out. Read =MEDIAN(B2:B4,B6:B8) and say what it returns. Six numbers now, so there is no single middle one.

Hint. Halfway between the third and fourth of six.
4

The shoe shop wants to know which size it sells most. In D11, use MODE on the sizes in D2 to D9.

Hint. D2 to D9.
5

In B12, write a formula with AVERAGE and MEDIAN that shows how far the street's average is above its median.

Hint. One minus the other.
6

Last one. The mansion at No. 7 is revalued at ten times the figure, so B5 becomes 14500. Read =ROUND(AVERAGE(B2:B8),0) with that change and say what the street's average becomes.

Hint. Seven prices, one of them enormous.