How To Find The Median In Excel
Using The Function “MEDIAN”

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Finding the average of a set of numbers is a common task—and the AVERAGE function makes it very easy.

Finding the median is a rarer occurrence.

But Excel also has a function to help you with that 🙂

Let’s take a look at the MEDIAN function, what it does, and some special cases that you should be aware of.

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

To see exactly how the MEDIAN function works, it can be helpful to have some data to work on.

We’ve put together a sample workbook for you to download.

Hit the button below to get it for free, and follow along with the rest of the article!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Finding the median

Finding the median of a set of numbers is very straightforward. Here’s the syntax of the function that will get it for you:

The syntax of the MEDIAN function

=MEDIAN(number1, [number2]…)

The first argument, number1, is required. It can be a typed number, a cell reference, or a logical statement.

number2 is the exactly the same, but it’s optional. MEDIAN can take up to 255 arguments, though you’ll usually apply it to a cell range.

The median is the number that appears in the middle of the list. In the sequence 10, 12, 15, 16, 17, the median is 15.

(We’ll talk about what happens when you have an even number of entries in a moment.)

Let’s use this function to find the median of the home values listed in the first sheet of the example workbook.

Click into any open cell, and type the following formula:

=MEDIAN(A2:A51)

Then hit Enter.

median-values

If you order the list smallest to largest, and count down to the middle, you’ll notice that the result of the formula ($584,461.85) isn’t in the list.

There’s an even number of list entries, so Excel takes the average of the middle two.

median-values-even

It’s a bit easier to see this with a smaller set of numbers. Try this:

=MEDIAN(2, 3, 3, 4, 4, 5)

Excel returns an answer of 3.5, even though there’s no 3.5 in the original list. We get this answer because Excel averages 3 and 4, the two middle values.

Finding the median of logical values

While you’ll most often look for the median of a series of numbers, the MEDIAN function can also evaluate logical statements.

Take the logical statement 1<2, for example. Excel evaluates this statement to TRUE, which is equivalent to a numerical value of 1. The statement 1>2 is FALSE, and Excel treats it as a 0.

Try this formula:

=MEDIAN(1<2, 1>2)

median-logical

The first argument is treated as a 1, and the second as a 0. Because there’s an even number of arguments, they’re averaged—leaving us with 0.5.

Other values in MEDIAN

So far, we’ve seen how MEDIAN works with cell references, numbers, and logical statements. But it’s worth taking a look at exactly how MEDIAN deals with various types of data.

The following types of data work with MEDIAN:

  • Numbers typed into the formula
  • Numbers in cell references or arrays
  • Logical statements typed into the formula

The following types of data are ignored by MEDIAN:

  • Logical statements in cell references or arrays
  • Text
  • Empty cells

Note that cells containing zeroes aren’t considered to be empty; they’re counted as zeroes. So if you want empty cells to count as zeroes for the purpose of your calculations, you’ll need to insert the digits.

Keep this list in mind if you’re trying to find the median of a list that includes multiple types of data.

Most of the time, you’ll use MEDIAN to find the middle value in a list of numbers, and you won’t need to worry about other types of data.

But it’s nice to know, just in case.

Kasper Langmann, Co-founder of Spreadsheeto

Find the median in no time

Instead of ordering a set of numbers from smallest to largest and counting down to the middle, you can use the MEDIAN function to get that information in a snap.

And now that you know which types of data work with the function, you’ll be able to use it properly and get the results you expect!