**Median Formula Excel (Table of Contents)**

## Guide to MEDIAN Formula in Excel

I am sure your math’s teacher must have eaten your head while teaching Mean, Mode, and Median formulas. But we have so many sophisticated technologies to readily calculate the MEDIAN value and excel is one of that software. In today’s article, we will talk about the MEDIAN formula in excel.

In excel we have a built-in excel function called MEDIAN which can return the MEDIAN of the supplied number series. The syntax is as follows.

It is straight forward Syntax, what are the numbers is the **Number 1 **argument. IF the numbers are selected as ranges then **Number 1 **is enough, If the individual cell is selected then we need to keep selecting each number as the separate argument, we can enter up to 255 arguments like this.

If the range is selected in **Number 1 **then all the other arguments become optional.

### What is the MEDIAN?

Let me tell you what the median is. MEDIAN in the exact middle value of the data series which are sorted numbers and sort should be from lowest to highest.

**Example 1:** look at the below numbers.

10, 15, **25**, 30, 35

In the above data series, **25 **is the MEDIAN number because 25 is the exact middle value of the number series.

**Example 2:** Suppose if you have numbers like this.

15, 10, 20, 30, 5.

The above numbers are not in order, so firstly we need to sort these numbers in order from lowest to highest.

5, 10, 15, 20, 30

Now the MEDIAN value is **15.**

**Example 3: **Suppose if we don’t have the exact middle-value number then we need to apply a different approach to find the MEDIAN value. Take a look at the below example of numbers.

5, 10, 15, 20, 30, 35

Here we have totally 6 numbers and cannot say which is the middle number exactly. Take two numbers from the middle.

4.9 (1,353 ratings)

5, 10, **15, 20**, 30, 35

15 & 20 are two numbers.

Add these two numbers and divide the result by 2.

(15+20)/2

=17.5

So, 17.5 is the MEDIAN number here.

**How to Use Median Formula in Excel with Examples?**

Below are some of the examples of Median Formula in Excel.

#### Excel Median Formula – Example #1

To start off let’s take a look at the simple example. Copy and paste the below numbers to your excel sheet.

Open excel MEDIAN function in A9 cell, select the above cells as the range.

So, the MEDIAN value is 17.5.

#### Excel Median Formula – Example #2

We can also get the middle date from the series of many dates. Consider below dates for example.

Copy this data to excel worksheet.

Apply the excel MEDIAN formula in the C2 cell.

So, the middle date is 21-Dec-2017.

### MEDIAN with IF Condition

Unfortunately, we don’t have logical condition formula for MEDIAN unlike other arithmetic means AVERAGEIF and AVERAGEIFS.

But we can use MEDIAN & IF excel condition as an array formula to do the logical test in excel. For an example copy the below data into excel sheet.

Now from the above list of numbers, we need MEDIAN value only for category A, B, & C.

This is a bit different from the regular MEDIAN calculation. Earlier we used to apply MEDIAN overall value and getting the MEDIAN value.

But we need MEDIAN value for each category. For example, for the category, A MEDIAN value is 113.

Since we don’t have MEDIANIFS function we can combine MEDIAN with IF condition to get the job done.

**Step 1: **Open MEDIAN & IF function one after the other.

**Step 2: **Logical test will be a category is equal to A, B, or C.

**Step 3: **If the logical test is TRUE then we need MEDIAN Value.

**Step 4: **if the logical test is FALSE, we need nothing (“”).

**Step 5: **We need to make this formula as an array formula, so close the formula by holding **Ctrl + Shift + Enter. **As soon as you close the formula we will see curly brackets ({}) on either end of the formula.

**Step 6: **Copy and paste the median excel formula to the remaining 3 category cells.

Ok, we got MEDIAN value for each category values separately.

**Things to Remember**

- If the data series is ODD then we will get the middle value of the data series.
- If the data series is EVEN then we will get the middle value as the average of the middle two numbers.
- If there are zero values then this also is considered for calculation purpose.
- MEDIAN with IF condition needs to be closed as an array formula by holding
**Ctrl + Shift + Enter**

### Recommended Articles

This has been a guide to Median Formula in Excel. Here we discuss how to use Median Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

## Leave a Reply