Skip to content

Excel function · Statistics

Excel MEDIAN function

MEDIAN returns the value that falls exactly in the middle when the numbers are sorted. In lists of prices, durations or amounts where a few extreme values distort the average, it is a more reliable summary.

In Turkish Excel: ORTANCA

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

English Excel=MEDIAN(number1, [number2], ...)
Turkish Excel=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

ArgumentRequiredDescription
number1RequiredThe first number, cell or range.
number2OptionalOptional 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.

E2fx=AVERAGE(C2:C7)
A delivery time that ignores the outlier: example table
ABCDEF
1OrderCourierDaysAverageMedian
2S-1001Swift Post27.22.5
3S-1002Swift Post3
4S-1003Swift Post2
5S-1004Swift Post30
6S-1005Swift Post2
7S-1006Swift Post4
  • 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.

D2fx=MEDIAN(B2:B5)
Median of an even number of values: example table
ABCD
1SupplierQuoteMedian
2Brown & Sons48,00051,500
3Northgate Ltd52,500
4Harbour Trade61,000
5Star Market50,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.

F2fx=MEDIAN(IF($B$2:$B$8=E2,$C$2:$C$8))
Median time for each courier: example table
ABCDEF
1OrderCourierDaysCourierMedian
2S-1001Swift Post2Swift Post2.5
3S-1002Northern Freight4Northern Freight5
4S-1003Swift Post3
5S-1004Northern Freight6
6S-1005Swift Post30
7S-1006Northern Freight5
8S-1007Swift Post2
  • 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

Type this in Nexell

Make a summary that shows each courier's median and average delivery time side by side

Convert to table

Bring 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.

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free