# How to calculate Median based on a criteria (MEDIAN – IF)

When it comes to calculating the median of the data based on a criteria then Excel does not have the ability in the median function to achieve this goal. So to get it done, we need to use **IF() **along with **MEDIAN(). **In this tutorial, we’ll learn how to use the combination of these two functions to calculate the median of the data based on a special criteria.

Let’s see the data set for which we’ll calculate the median based on a criteria. Our data set consists of the details of the marks of two students and we wish to calculate the median of the marks obtained by a specific student whose name will be chosen in a specific cell.

So now we’ll calculate the median of the marks obtained by the student depending upon the name of the student.

Excel provides us with a lot of functions and tools to analyse data sets. The nature of the data can vary and the type of analysis required on the data also varies with the nature of the data. Often we have to perform statistical analysis of the data and Excel has many standard functions for this purpose. One of the basic statistical functions is the **MEDIAN()**, which is used to calculate the median of the given data.

### Step 1 – Create a Named Range for the formula

– We’ll create a named range consisting of the names of the students from the available data so that our formula can work properly.

– To create the named range, select all the names. Go to the ** Name Box **and write

**Names.**The text shown in the

**will be the name of the range of names from**

*Name Box***A2:A10.**

### Step 2 – Create the appropriate formula using MEDIAN-IF

– Now we can use the combination of both functions in the formula to get our desired results. The formula will be as follows;

**=MEDIAN(IF(Names=D3,C2:C10))**

– Our formula calculates the median of the scores of the particular student based on the name of the student present in the cell **D3. **We can see that the median of the score changes when the name of the student is changed.

### Step 3 – Manual Median

– We can calculate the median of the same data manually to check whether our formula calculated the results properly or not. However, we’ll get the same answers.

## Breakdown of the formula:

We used the formula;

**=MEDIAN(IF(Names=D3,C2:C10))**

Let’s break down the formula from inside. The goal is achieved by using the **IF** function which checks for the value of cell **D3 **in the named range ** Names. **When the value of cell

**D3**matches the name in the named range the corresponding data values are selected from the data range

**C2:C10.**The output of the

**IF(Names=D3,C2:C10)**as follow;

**{****97;53;62;65;****FALSE;FALSE;FALSE;FALSE;FALSE****}**

When this array is passed on to the **MEDIAN() **function it calculates the median of the numeric values only and returns the correct value. Similarly in case of choosing **Tom **in **D3**, we’ll get the following array as the output of **IF(Names=D3,C2:C10)** and the **MEDIAN() **function will again calculate the median of the numeric values only.

** {****FALSE;FALSE;FALSE;FALSE;****95;67;45;88;94****}**