**How To Find The Median In Excel**

Using The Function “MEDIAN”

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.

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

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

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)**

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.

**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!