MEDIAN returns the middle number in a set of values, so half the numbers are above it and half are below it. Unlike AVERAGE, it is not pulled off course by a few extremely high or low values.
=MEDIAN(number1, [number2], …)What does MEDIAN do?
MEDIAN is a statistical function that gives you the middle value of a group of numbers. Put simply, if you line up all the numbers from smallest to largest, MEDIAN returns the one in the centre. When there is an even count of numbers, it returns the average of the two middle values. Text, logical values and empty cells inside a range are ignored, so you can point it at a normal column of data without cleaning it first.
The function matters because averages can lie. A single very large sale, a typo like 100000 instead of 1000, or one unusually slow day can drag an AVERAGE far away from what a typical value looks like. MEDIAN stays anchored to the centre, which makes it a safer summary for salaries, house prices, delivery times and anything else with a long tail of extreme values.
A common real situation: you manage a support team and want to report typical ticket resolution time. Most tickets close in 2 to 5 hours, but a handful of complex cases take 40 hours. AVERAGE reports something like 9 hours, which no customer actually experiences. MEDIAN reports 3.5 hours, which is what a typical ticket really looks like. That is the number worth putting in your report.
When to use MEDIAN
- Reporting typical salaries or pay rates when a few executives earn far more than everyone else.
- Summarising house prices in a neighbourhood where one mansion distorts the average.
- Finding the typical delivery time or ticket resolution time when a few extreme cases exist.
- Comparing student test scores when one very low or very high score would skew the class average.
- Setting a realistic benchmark or target from historical data that contains outliers.
- Checking whether a dataset is skewed by comparing MEDIAN with AVERAGE.
MEDIAN syntax
=MEDIAN(number1, [number2], …)| Argument | Required? | What to enter |
|---|---|---|
number1 | Required | The first number, cell reference or range you want to evaluate. Example: B2:B10 or 150. |
number2 | Optional | Additional numbers, cells or ranges, up to 255 in total. Example: C2:C10 to include a second column. |
What it returns: A single number: the middle value of the supplied numbers. If the count of numbers is even, it returns the average of the two middle values. If no numbers are supplied, it returns 0.
MEDIAN examples
1 Example 1: The middle score in a small class
A teacher has five test scores and wants the middle one rather than the average. The formula =MEDIAN(B2:B6) lines up 55, 62, 70, 88 and 91 and returns the third value, 70. Because there are five numbers, there is one exact middle. This is the simplest use of MEDIAN and shows how it differs from AVERAGE, which would return 73.2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | |||
| 2 | Ana | 55 | 70 | ||
| 3 | Ben | 62 | |||
| 4 | Cara | 70 | |||
| 5 | Dan | 88 | |||
| 6 | Eve | 91 | |||
| 7 |
2 Example 2: Even count of delivery times
A logistics analyst records six delivery times in hours and wants the typical time. The formula =MEDIAN(B2:B7) sorts the values to 2, 3, 4, 5, 6 and 9. With an even count, MEDIAN averages the two middle values, 4 and 5, and returns 4.5. That is more representative than the AVERAGE of 4.83, which is pulled up by the 9-hour delivery.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Hours | |||
| 2 | ORD-101 | 5 | 4.5 | ||
| 3 | ORD-102 | 3 | |||
| 4 | ORD-103 | 9 | |||
| 5 | ORD-104 | 2 | |||
| 6 | ORD-105 | 6 | |||
| 7 | ORD-106 | 4 | |||
| 8 |
3 Example 5: Typical ticket resolution time ignoring text
A support lead tracks ticket resolution hours, but one cell contains the word Pending instead of a number. The formula =MEDIAN(B2:B8) ignores the text and evaluates the six numeric values: 2, 3, 4, 5, 6 and 40. With an even count it averages the two middle values, 4 and 5, and returns 4.5. The AVERAGE would fail or be badly skewed, but MEDIAN gives a clean typical time.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Ticket | Hours | |||
| 2 | T-1001 | 3 | 4.5 | ||
| 3 | T-1002 | 4 | |||
| 4 | T-1003 | 2 | |||
| 5 | T-1004 | 5 | |||
| 6 | T-1005 | 6 | |||
| 7 | T-1006 | 40 | |||
| 8 | T-1007 | Pending | |||
| 9 |
Common MEDIAN errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | One of the arguments is text that cannot be interpreted as a number, such as a cell containing N/A passed as a direct argument rather than inside a range. | Pass whole ranges like B2:B10 so text is ignored, or clean the text values before using MEDIAN. |
| #NUM! | No numeric values were found in the supplied arguments, for example when the range is entirely empty or contains only text. | Check that the range contains at least one number and that you have selected the correct column. |
| Wrong result (0 or unexpected number) | Numbers stored as text are ignored by MEDIAN, so they are silently left out of the calculation. | Convert text-formatted numbers to real numbers using Text to Columns or by multiplying by 1, then recalculate. |
| #NAME? | The function name is misspelled, such as =MEDAIN(B2:B10). | Type =MEDIAN( and let Excel's formula suggestions complete the name correctly. |
Tips for using MEDIAN
- Compare MEDIAN with AVERAGE on the same range: a large gap between them signals outliers or a skewed distribution.
- Use whole ranges such as
B2:B100rather than individual cells so blanks and text are ignored automatically. - MEDIAN works on filtered data only if you use it with visible cells; for filtered lists consider
SUBTOTALorAGGREGATEalternatives. - For an even count of values, remember MEDIAN returns the average of the two middle numbers, which may be a decimal.
- Combine MEDIAN with
IFinside an array formula to get a conditional median, for example the median of only North region sales. - Keep dates as real Excel dates if you want a median date; MEDIAN returns a serial number that you can format as a date.
Frequently asked questions
What is the difference between MEDIAN and AVERAGE in Excel?
AVERAGE adds all values and divides by the count, so extreme values pull it up or down. MEDIAN finds the middle value after sorting, so it stays representative even when a few outliers exist.
Does MEDIAN ignore text and blank cells?
Yes. When you supply a range, MEDIAN ignores text, logical values and empty cells and calculates only from the numbers. If you type text directly as an argument, however, Excel may return #VALUE!.
Why does MEDIAN return a decimal when my data are whole numbers?
When the count of numbers is even, there is no single middle value, so MEDIAN averages the two middle numbers. That average can be a decimal even if every original value is a whole number.
Can MEDIAN work with dates?
Yes. Excel stores dates as serial numbers, so MEDIAN returns the middle date as a serial number. Format the result cell as a date to display it correctly.
What happens if the range contains no numbers at all?
MEDIAN returns 0 if the arguments contain no numbers, and may return #NUM! in some cases. Always check that your range includes at least one numeric value.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. MEDIAN reference