Excel MEDIAN Function: The Middle Value, Not the Average
Returns the middle value of a set of numbers when they are put in order.
MEDIAN sorts the numbers and returns the one in the middle. With an even count there is no single middle, so it averages the two closest to it. That is the whole definition, and the reason it matters is what it does *not* do: it does not move when an extreme value appears.
Compare the two on the salary column here. The average is dragged well above what almost everyone earns by one executive salary; the median sits among the actual salaries and describes a typical employee. Neither number is wrong, but one answers "what does a person here earn?" and the other does not.
The rule of thumb is simple. When the data is roughly symmetrical, average and median agree and either will do. When they disagree noticeably, the data is skewed and the median is almost always the honest figure to publish — which is exactly why salary, house price and response time are all reported as medians.
Syntax
=MEDIAN(number1, [number2], …)Arguments
number1Required- The first range or value. Text, blanks and logicals are ignored, as with AVERAGE.
number2, …Optional- Further ranges or values, up to 255.
The example data
Headers in row 1, data in A2:C8. Note how far F8's salary sits above the rest.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Salary | Team |
| 2 | Alice Moreau | 32000 | Support |
| 3 | Bruno Santos | 28000 | Support |
| 4 | Chen Wei | 45000 | Engineering |
| 5 | Dana Okafor | 28000 | Support |
| 6 | Erik Halls | 38000 | Engineering |
| 7 | Farah Idris | 41000 | Engineering |
| 8 | Greg Nolan | 154000 | Executive |
Worked examples
=MEDIAN(B2:B8)Result: 38000
The middle of seven salaries once sorted. Four people earn less, two earn more.
=AVERAGE(B2:B8)Result: 52285.71
The same data as a mean. One executive salary pulls it above six of the seven people it describes.
=MEDIAN(B2:B5)Result: 30000
An even count of four, so the two middle values (28000 and 32000) are averaged.
=MEDIAN(IF(C2:C8="Engineering", B2:B8))Result: 41000
A conditional median. There is no MEDIANIF, so this is an array formula — entered normally in Microsoft 365, with Ctrl+Shift+Enter before it.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#NUM!
Why it happens: The range contains no numbers at all.
How to fix it: Guard it in templates: =IFERROR(MEDIAN(range), "No data").
Median seems too low
Why it happens: Blank cells are excluded but zeros are not, so a column padded with zeros pulls the middle down.
How to fix it: Decide which you mean and filter accordingly: =MEDIAN(IF(range<>0, range)).
No MEDIANIF exists
Why it happens: Excel provides AVERAGEIF and SUMIF but never added a conditional median.
How to fix it: Use MEDIAN(IF(...)) as an array formula, or MEDIAN(FILTER(...)) if you have dynamic arrays — the second is far more readable.
Tips worth knowing
- Report median and average side by side. The gap between them is a one-glance measure of how skewed the data is.
- MEDIAN(FILTER(range, condition)) is the modern conditional median and needs no special key combination.
- QUARTILE and PERCENTILE generalise the idea: the median is simply the 50th percentile.
- Median is resistant to outliers but tells you nothing about spread — pair it with STDEV or a quartile range.
Frequently asked questions
When should I use MEDIAN instead of AVERAGE?
Whenever a few extreme values would distort the picture — salaries, house prices, order sizes, response times. If the average and the median differ noticeably, that difference is itself the signal that the average is being pulled by outliers.
What does MEDIAN return with an even number of values?
The average of the two middle values. With four numbers sorted as 28000, 28000, 32000, 45000, the median is the mean of the second and third, so 30000.
How do I calculate a median with a condition?
There is no MEDIANIF. In Microsoft 365 use =MEDIAN(FILTER(B2:B100, C2:C100="Engineering")). In older versions, =MEDIAN(IF(C2:C100="Engineering", B2:B100)) entered with Ctrl+Shift+Enter does the same job.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.