How To Calculate The Median In Excel

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:

Typing the MEDIAN function in Excel
Typing the MEDIAN function in Excel

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

Selecting the statistical category from the formulas tab to find the MEDIAN function
Selecting the statistical category from the formulas tab to find the MEDIAN function
Selecting the MEDIAN function in Excel from the statistical category
Selecting the MEDIAN function in Excel from the statistical category

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

Selecting the range of data as the Function Arguments of the MEDIAN function in Excel
Selecting the range of data as the Function Arguments of the MEDIAN function in Excel

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

Selecting the range of data as the Function Arguments of the MEDIAN function in Excel
Selecting multiple ranges for the MEDIAN function

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.

How to calculate The Median In Excel, The Median In Excel
The MEDIAN IF array formula

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

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

How to calculate The Median In Excel, MEDIAN IF example
MEDIAN IF example

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.

Subscribe to our Newsletter

Share this post with your friends

Leave a Reply

Your email address will not be published.