How To Calculate The Median In Excel

In simple words, the median is the number that divides your ascending sorted data set into two halves. 

Example: 1, 2, 3, 4, 5

In this example, the number 3 is the median because it splits our set of numbers into two equal halves: (1,2) and (4,5)

Note that the numbers are sorted in ascending order.

 

The Concept of The Median

The median is a measurement that represents the central tendency in statistics. The mean is also a measurement representing the central tendency; however, there are times when large outliers are present, where the mean is less accurate than the median.

Visit the How to Calculate Average in Excel blog to learn more about the mean (average).

How To Find the Median

Step 1: Sort the data in ascending order.

Step 2: Check to see whether the number of values is even or odd.

If odd: Find the number that splits the data into two halves like the example we saw earlier.

If even: The median is found by calculating the average of the two values that split the data into two equal groups:

Example: 1, 2, 3, 4, 5, 6

Median = (3+4) / 2 = 3.5

How To Find The Median In Excel

Fortunately, Excel has a built-in function for calculating the median. Conveniently, it’s called the MEDIAN function. Using this function is pretty straightforward, and I’ll explain it with an example. After that, we’ll move on to another useful concept of statistics, the MEDIAN IF. Suppose you need to calculate the median of a data set with a condition. In that case, you need to define the condition using the IF formula and mix it with the MEDIAN function. Continue reading to learn this with an example.

The MEDIAN Function In Excel

The easiest way to calculate the median in Excel is through the MEDIAN function:

MEDIAN (number1, [number2], …)

To calculate the median using this function:

Step 1: Select a cell in which you would like to showcase the median, then either type =MEDIAN:

Or head over to the formulas tab, select more functions and find MEDIAN in the statistical category:

Step 2: In the number 1 section, type in the range of numbers you want to include in the calculation.

A1:A6 represents the range that the program considers while finding the median.

Step 3: Click on the ok button to finish the process.

Note that If all your values are not in the same column, you can also insert multiple ranges in the formula by adding another range to it with a comma:

Example: =MEDIAN(A1:A6,B2:B5).

Median If In Excel

“Median If” is an array formula that helps you calculate a conditional median of your data by using MEDIAN and IF functions together.

For example, suppose you want to calculate the median of cells greater than 15.

Step 1: Like before,  select a cell where you would like to showcase the median and then type =MEDIAN. (Since unlike MEDIAN, MEDIAN IF is an array formula, you can’t select it from the formulas tab this time.)

Step 2: In order to separate the cells that meet your condition from those that do not, first, you need to define your condition. To do this, you need to use the IF function in Excel.

 In our example, we only want the cells with values greater than 15. So we write:

=MEDIAN(IF(A2:A15>15,A2:A15))

Step 3: Unlike normal functions run by pressing ENTER, array formulas are run by pressing CTRL+SHIFT+ENTER. That’s why array formulas are also called CSE functions.

Conclusion

In this blog, you learned how to calculate the median and median if in Excel with only 3 simple steps. Excel has the MEDIAN built-in function for calculating a simple median, and you can use the median if array formula for a conditional median.

You can connect with us and ask our experts for your inquiries and get more Excel Support Services.