What it does
MEDIAN sorts the numbers itself; you do not need to sort your list. With an odd number of values it returns the middle one; with an even number it returns the average of the two middle values: for 10, 20, 30 and 40 the result is 25.
The difference from AVERAGE shows with extreme values. If four of five orders arrived in 2 days and one in 30, the average delivery time is 7.6 days, although most orders really took 2; MEDIAN returns 2. Showing both side by side reveals at a glance whether a list has outliers.
Empty cells and text are skipped, while zeros count. For the most frequent value use MODE.SNGL, for quartiles QUARTILE.INC.
Syntax
=MEDIAN(number1, [number2], ...)=ORTANCA(sayı1; [sayı2]; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
number1 | Required | The first number, cell or range. |
number2 | Optional | Optional further numbers or ranges, up to 255 arguments. |
Examples
1A delivery time that ignores the outlier
One of six orders was held at customs and took 30 days. E2 returns the average, F2 the median.
=AVERAGE(C2:C7)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Courier | Days | Average | Median | |
| 2 | S-1001 | Swift Post | 2 | 7.2 | 2.5 | |
| 3 | S-1002 | Swift Post | 3 | |||
| 4 | S-1003 | Swift Post | 2 | |||
| 5 | S-1004 | Swift Post | 30 | |||
| 6 | S-1005 | Swift Post | 2 | |||
| 7 | S-1006 | Swift Post | 4 |
- E2
=AVERAGE(C2:C7) - F2
=MEDIAN(C2:C7)
A single delay pushes the average up to 7.2 days; the median is 2.5 days, close to what most orders really took.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Median of an even number of values
Four suppliers sent quotes. With an even number of values, MEDIAN returns the average of the two middle quotes.
=MEDIAN(B2:B5)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Supplier | Quote | Median | |
| 2 | Brown & Sons | 48,000 | 51,500 | |
| 3 | Northgate Ltd | 52,500 | ||
| 4 | Harbour Trade | 61,000 | ||
| 5 | Star Market | 50,500 |
Sorted, the two middle quotes are 50,500 and 52,500, so the median is 51,500. The list does not need to be sorted for this.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Median time for each courier
The formula in F2 is filled down. IF returns the days for rows whose courier matches column E and FALSE for the others; MEDIAN skips the FALSE values.
=MEDIAN(IF($B$2:$B$8=E2,$C$2:$C$8))| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Courier | Days | Courier | Median | |
| 2 | S-1001 | Swift Post | 2 | Swift Post | 2.5 | |
| 3 | S-1002 | Northern Freight | 4 | Northern Freight | 5 | |
| 4 | S-1003 | Swift Post | 3 | |||
| 5 | S-1004 | Northern Freight | 6 | |||
| 6 | S-1005 | Swift Post | 30 | |||
| 7 | S-1006 | Northern Freight | 5 | |||
| 8 | S-1007 | Swift Post | 2 |
- F2 (filled down to F3)
=MEDIAN(IF($B$2:$B$8=E2,$C$2:$C$8))
Swift Post's median is 2.5 days and Northern Freight's 5. Despite the 30-day delay, Swift Post's median stays low.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #NUM!
Cause: There are no numbers in the range; the cells are empty or the numbers are stored as text.
Fix: Check the range and how the numbers are stored.
- Median too low
Cause: Rows without data were filled with 0; the zeros join the sorting and pull the middle down.
Fix: Leave the cell empty when there is no data; empty cells are not counted.
Good to know
- Show the average and the median side by side: a large gap between them means the list has extreme values.
- For a conditional median in Microsoft 365, use
=MEDIAN(FILTER(C2:C50,B2:B50="West")).
In Nexell
Get Nexell to do it from a sentence
Make a summary that shows each courier's median and average delivery time side by side
Convert to tableBring your delivery list into the tool and type this. Nexell builds the summary with real Excel formulas and shows it in the preview first; in the downloaded file, the summary follows when you enter new delivery times.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between median and average?
The average adds all values and divides by the count, so one very large value pulls it up. The median is the middle value of the sorted list and is not affected by extremes.
How do I calculate a conditional median in Excel?
In Microsoft 365: =MEDIAN(FILTER(C2:C50,B2:B50="West")). Without FILTER, use =MEDIAN(IF(B2:B50="West",C2:C50)); in older versions enter it as an array formula with Ctrl+Shift+Enter.