How To Calculate The Median In Excel

3.8 min read|Last Updated: March 24th, 2024|Categories: excel|
table of content

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