The MEDIAN and MODE.SNGL functions in Excel
MEDIAN sorts the numbers behind the scenes and returns the one in the middle. With an even count it averages the two middle values.
MODE.SNGL returns the value that appears most often. The older MODE function does the same and still works, and MODE.MULT returns all the values in a tie.
Syntax
=MEDIAN(number1, [number2], …)
| Argument | What it means |
|---|---|
| number1 | A number or a range of numbers. |
| number2, …optional | More numbers or ranges. Text and empty cells in a range are ignored. |
Examples
1. The middle score
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Median | |
| 2 | Ana | 70 | 85 | |
| 3 | Ben | 85 | ||
| 4 | Cy | 85 | ||
| 5 | Dee | 90 | ||
| 6 | Eli | 100 |
Sorted: 70, 85, 85, 90, 100. The middle one is 85.
2. An even number of values
| A | B | C | |
|---|---|---|---|
| 1 | Value | Median | |
| 2 | 10 | 25 | |
| 3 | 20 | ||
| 4 | 30 | ||
| 5 | 40 |
There is no single middle value, so the median is the average of the two in the middle: (20 + 30) / 2.
3. The most common value
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Most common | |
| 2 | Ana | 70 | 85 | |
| 3 | Ben | 85 | ||
| 4 | Cy | 85 | ||
| 5 | Dee | 90 | ||
| 6 | Eli | 100 |
85 appears twice and every other score once.
4. A salary list with one extreme value
| A | B | C | |
|---|---|---|---|
| 1 | Salary (k) | Median | |
| 2 | 30 | 35 | |
| 3 | 32 | ||
| 4 | 35 | ||
| 5 | 36 | ||
| 6 | 200 |
The 200 would pull the average up to 66.6, but the median stays at 35, closer to what most people earn.
Common mistakes
Reporting the average of skewed data. One extreme value makes AVERAGE unrepresentative.
Fix: Check the data with MEDIAN too, and report both if they differ a lot.
Expecting MODE.SNGL to find a most common text. It only works with numbers, and returns #N/A when no value repeats.
Fix: For text, count with COUNTIF or use INDEX with MATCH on the counts.
Frequently asked questions
Is there a median with a condition?
There is no MEDIANIF. In Excel 2021 and newer use =MEDIAN(FILTER(B2:B20,A2:A20="East")).
Why does MEDIAN ignore my text numbers?
Numbers stored as text are skipped inside a range. Convert them with VALUE or Text to Columns.