MEDIAN and MODE

1 / 4

One big catering order in B2 pulls the average up to about £32, more than most customers spend.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABCDEFG
1CustomerSpendCupsMeasureValue
2Tariq2221Average spend31.576
3Nadia6.032Median spend
4Daniel7.466Most common cups
5Quinn9.871
6Jamal6.053
7Isla14.932
8Kofi15.134
9Yusuf7.322
10Sam18.426
11Rosa8.552
12
13
14
15
big order
average
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How MEDIAN and MODE works

  • What is the median?

    Put the numbers in order and take the one in the middle. With an even count there are two in the middle, and the median is halfway between them.

    =MEDIAN(B2:B11)

  • Why not just use the average?

    One very big or very small number pulls the average towards it. A single catering order of £200 makes the "average" spend look far bigger than what most customers pay. The median ignores how far out the extremes are, so it shows a typical value. That's why house prices and salaries are usually given as medians.

  • What is the mode?

    The value that appears most often: the most common shoe size, or the number of cups most orders have.

    =MODE(C2:C11)

    If no value appears twice, MODE shows #N/A. If two values tie, it gives the one that appears first. Newer Excel calls it MODE.SNGL, and both names work.

Know someone fighting with a spreadsheet? Send them this page.