The MEDIAN function

Returns the middle value of a set of numbers. With an even count it averages the two in the middle.

How to use MEDIAN

The median is the middle value when you line the numbers up from smallest to largest. Half of the numbers are below it and half are above it. Picture a row of houses sorted by price: the median is the price of the house standing in the middle.

Use MEDIAN when a few extreme values would distort an average, for example salaries on a staff list, order sizes, delivery times or house prices. One huge value pulls AVERAGE up, but MEDIAN hardly moves. =MEDIAN(C2:C6) finds the middle sale in the list. You do not need to sort the data first, because MEDIAN does the ordering behind the scenes.

With an odd number of values, the median is one of them. With an even number, it is the average of the two in the middle, so =MEDIAN(1,2,3,10) gives a number that is not in the list. Text and empty cells in a range are skipped.

Put MEDIAN next to AVERAGE. If the two are far apart, your data has outliers, and the median is usually the fairer picture of a typical value. To pick a value by its position instead, use LARGE or SMALL.

Syntax

MEDIAN(number1, [number2], ...)
number1
The first number, cell or range.
number2 (optional)
More numbers or ranges.

Examples

E1=MEDIAN(C2:C6)
ABCDE
1RegionProductSales300
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180
B1=MEDIAN(1,2,3,10)
AB
12.5

Common MEDIAN mistakes

  • MEDIAN and AVERAGE give very different numbers

    That is not an error. One very large or very small value pulls the average toward it, while the median stays near the middle. Check the list for an outlier, and decide which of the two answers your question.

  • MEDIAN ignores some of my numbers

    Numbers stored as text are skipped, just like in SUM. They sit on the left side of the cell. Convert them with =VALUE(C2) in a helper column and take the median of that column.

  • MEDIAN is pulled down by zeros

    An empty cell is skipped, but a zero is a real number and counts. If the zeros mean "no value", delete them, or let the formula that fills the cell return empty text with "" instead of 0.

  • MEDIAN returns #NUM!

    The range holds no numbers at all, only text or empty cells, so there is no middle to find. Check that you pointed at the right cells and that the numbers are not stored as text.

Questions

What is the difference between MEDIAN and AVERAGE?

AVERAGE adds all the numbers and divides by how many there are, so one extreme value moves it a lot. MEDIAN takes the middle value after sorting, which makes it steadier when the data has outliers.

What does MEDIAN do with an even number of values?

It averages the two middle values. =MEDIAN(1,2,3,10) averages 2 and 3 and gives 2.5.

Is there a MEDIANIF function?

No. In Excel 365 you can use =MEDIAN(IF(A2:A6="North",C2:C6)). In older versions of Excel, confirm that formula with Ctrl+Shift+Enter. In Google Sheets, wrap it in ARRAYFORMULA.

Does MEDIAN ignore blanks and text?

In a range, yes: empty cells and text are skipped. Zeros and numbers you type straight into the formula are counted.

Related functions