Function guides ยท Statistical
MEDIAN function
Returns the middle value of a set of numbers.
Syntax
=MEDIAN(number1, [number2], ...)
| Argument | What it is |
|---|---|
number1 | A number, cell reference, or range. |
number2, ... | Optional. Up to 255 more. |
What MEDIAN does
MEDIAN returns the middle number of a set. Half the values are above it and half below.
Averages can mislead. One huge value among small ones pulls the average toward itself, while the median hardly moves. That is why reports on salaries, house prices and waiting times quote medians.
The data does not need to be sorted. For an odd count, MEDIAN returns the value in the middle. For an even count, it averages the two middle values.
There is no conditional version built in, so a group median uses an IF inside MEDIAN.
Pair MEDIAN with AVERAGE as a quick check on skew. If they are close, the data is fairly even. If the average is much higher than the median, a few large values are pulling it up, and the median is the better description of a typical item. Salary tables, order values and delivery times nearly always behave this way.
QUARTILE and PERCENTILE extend the same idea: MEDIAN is the 50th percentile, and the quartiles mark the 25th and 75th. A box plot summary is the minimum, first quartile, median, third quartile and maximum.
MEDIAN examples
1. Middle salary
| A | B | |
|---|---|---|
| 1 | Role | Salary (k) |
| 2 | Clerk | 42 |
| 3 | Analyst | 51 |
| 4 | Buyer | 45 |
| 5 | Manager | 48 |
| 6 | Director | 220 |
| 7 | 48 |
Sorted, the five salaries run 42, 45, 48, 51, 220. The middle one is 48. The average would be 81.2, pulled up by one executive.
2. An even count averages the middle two
| A | B | |
|---|---|---|
| 1 | Response time (min) | |
| 2 | 20 | 8 |
| 3 | 4 | |
| 4 | 9 | |
| 5 | 7 |
With four values there is no single middle one, so MEDIAN averages the two in the center. Sorted: 4, 7, 9, 20. The middle two are 7 and 9, which average 8.
3. Median for one group
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sale | ||
| 2 | West | 450 | 450 | |
| 3 | East | 900 | ||
| 4 | West | 620 | ||
| 5 | East | 310 | ||
| 6 | West | 275 |
MEDIAN has no criteria argument. An IF inside an array formula picks the West rows first: 450, 620 and 275 give a median of 450. In older Excel this must be confirmed with Ctrl+Shift+Enter, and Microsoft 365 handles it directly.
Common MEDIAN mistakes
- Using AVERAGE with outliers. A single extreme value drags the mean. House prices, salaries and response times are usually reported as medians.
- Expecting MEDIAN to ignore zeros. Zeros are values. If zero means missing, leave the cell empty or filter with IF.
- Conditional median without array entry. In Excel 2019 and earlier, MEDIAN(IF(...)) needs Ctrl+Shift+Enter, or it returns #VALUE! or a wrong value.
- Sorting first. The data does not need sorting. MEDIAN does it internally.
MEDIAN on Excel job tests
Medians appear as the alternative to AVERAGE: "calculate the median order value", or "say whether the mean or the median better represents this data". When the data has an outlier, the two differ.
A median for one group has no built-in function. The usual answer is MEDIAN(IF(range=value, values)). In Excel 2019 and earlier it needs Ctrl+Shift+Enter, which shows curly braces; Microsoft 365 does not.
With an even count of values, the result is the average of the middle two. With an odd count the result is always one of the data points; with an even count it can land between two of them, so a result that is not in your list is normal there.
Related functions
MEDIAN questions
- What is the difference between mean and median?
- The mean adds and divides. The median is the middle value after sorting, so extreme values barely move it.
- What does MEDIAN do with an even number of values?
- It averages the two middle values.
- How do I calculate a median with a condition?
- Use
=MEDIAN(IF(range=criteria,values)). Confirm with Ctrl+Shift+Enter in Excel 2019 and earlier. - Does MEDIAN ignore blank cells?
- Yes. Blanks and text are ignored, but zeros count.
Practice MEDIAN
Try it in a working grid: the free lesson MEDIAN: the middle value (module 20 of Analyst) has checked MEDIAN exercises.
Practice this function