## MEDIAN Formula in Excel

Median is the middle number from a data set, it represents the most middle value which separates the data set in one set of lower values and one set of higher values, in excel we have an inbuilt formula to calculate median which is used by typing the keyword =Median and providing the numbers as arguments in it.

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.

### How to Use Median Formula in Excel? (with Examples)

#### 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 MEDIAN function in excel A9 cell, select the above cells as the range.

So, the MEDIAN value is 17.5.

#### Example #2

We can also get the middle date from the series of many dates. Consider the 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 Excel Function 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 function & IF excel condition as an array formula to do the logical test in excel. For example, copy the below data into an excel sheet.

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

This is a bit different from the regular MEDIAN calculation. Earlier we used to apply MEDIAN excel function 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:**The 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 purposes.
- 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 –

- Quick Analysis Tools in Excel
- Create Formula in Excel
- Explanation of the Weighted Mean Formula
- Mean vs Median – Compare
- Harmonic Mean Formula
- Calculate Mode in Excel
- Analysis ToolPak in Excel

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion