MEDIAN and MODE in Excel: how to use it
MEDIAN gives the middle value and MODE the value that appears most often. Both are less sensitive to outliers than the average.
Try the first exercises for freeIn the lesson MEDIAN and MODE
What do MEDIAN and MODE do?
MEDIAN mentally puts the numbers in order and gives the middle one. With an even number of values, Excel takes the average of the two in the middle. MODE gives the value that appears most often.
One outlier can shift the average a lot: one very high salary pulls the average up. The median isn't bothered by that, so it often gives a fairer picture of what's normal.
When do you use MEDIAN and MODE?
- The typical salary in a team, without one top salary distorting everything: =MEDIAN(B2:B20).
- The typical price of a house in an area: =MEDIAN(C2:C80).
- The shoe size you sell most often: =MODE(B2:B200).
- The mark given most often in a test: =MODE(C2:C30).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The middle value when you put everything in order.
Filled in: =MEDIAN(C2:C6)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
MEDIAN | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | MEDIAN |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
number1 | A range of numbers, such as C2:C6. | C2:C6 |
[number2] | Optional: more cells or ranges, separated by commas. | left out |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
MEDIAN and MODE step by step
E1 contains =MEDIAN(C2:C6). The middle one of the five revenues. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click E1 and type MEDIAN with a bracket.
=MEDIAN( - Select the revenue figures, C2 to C6.
=MEDIAN(C2:C6 - Close the bracket and press Enter: in order, they're 75, 340, 410, 980 and 1200, and the middle one is 410.
=MEDIAN(C2:C6)
More examples
An even number of values
C1 contains =MEDIAN(A2:A5). With four numbers, two are in the middle, 20 and 30. MEDIAN takes their average: 25. The average of all four is 40.
What appears most often
C1 contains =MODE(A2:A7). Size 42 appears three times, more often than any other size.
Median versus average
C1 contains =AVERAGE(A2:A6)-MEDIAN(A2:A6). One high salary pulls the average up to 4000, while the median stays at 2600: a difference of 1400.
Median or average?
The average revenue here is 601, but half of them are below 410. For salaries and prices, the median often gives a fairer picture.
Where it often goes wrong
- With an even number of values, MEDIAN takes the average of the two middle ones.
- MODE gives #N/A if no value appears twice.
Practise this in the lesson MEDIAN and MODE. The first exercises are free.
Frequently asked questions
What is MODE.SNGL?
The newer name of the same function. Both work.
Read next
ROUNDCEILING and FLOORMOD and INTLARGE and SMALLSEQUENCE and RANDBETWEEN