How to calculate the median in MS Excel

Proficiency in calculating the median is crucial for those involved in numbers and data manipulation using Excel. Serving as a measure of central tendency, the median identifies the midpoint within a dataset. Its resilience to extreme values or outliers distinguishes it from the mean, rendering it a reliable tool for data analysis. The MEDIAN function automatically sorts the dataset.

To calculate the median accurately, the dataset should be sorted in ascending or descending order. Sorting the data allows us to identify the middle value or the average of the two middle values, depending on whether the dataset has an odd or even number of elements. For example, consider a dataset like 10, 20, 30, 40, 50. In this case, the median is 30. Alternatively, for a dataset such as 5, 10, 15, 20, the median is calculated as (10 + 15) / 2, resulting in 12.5.

Syntax

The basic syntax for the MEDIAN function is as follows:

Here, number1 and number2 represent the cell number or any numeric value. It’s worth noting that we have the flexibility to include additional cells as needed.

Note: Ranges or cell references may be used instead of explicit values.

Simple values

If we simply want to find out the median between 10, 20, and 30, we'll use the MEDIAN function using the formula given below:

List of cells

If we want to find MEDIAN by providing the list of cells, we can use the formula given below:

Range of cells

If we want to find MEDIAN by providing the range of cells, we can use the formula given below:

If we want to use the range of cells, and want to skip a cell number in that case we can use the formula given below:

The above formula calculates the median of cells A1, A2, A3, and A5.

Steps

The following is a step-by-step guide to using the MEDIAN function:

1. Select the cell: Click on the cell where we want the median to appear.

2. Use the MEDIAN function: Type =MEDIAN( into the selected cell.

3. Select the range: If we want to calculate the median of the number of cells in the range A1 to A5, there are two ways to achieve the desired result:

   1. We can enter “A1:A5” after the opening parenthesis and then close the parenthesis and press the “Enter” button. The formula should look something like this: =MEDIAN(A1:A5).

   2. We can also enter “A1,A2,A3,A4,A5” after the opening parenthesis and then close the parenthesis and press the “Enter” button. The formula should look something like this: =MEDIAN(A1,A2,A3,A4,A5).

Let’s now apply the above steps to our dataset. Let’s suppose we have the following dataset:

Now, our objective is to determine the median of the marks a student gets in the required dataset. We have seen two ways to calculate the median. Let’s use both ways:

Calculating the median with a list of cells

First, we select the cell, where we need to calculate the median. Let’s suppose here the cell number is B9. Then we select the “cell(B9)” and write the following in the cell:

Let’s visualize the above steps in action.

Note: As you can see in the above slide, when multiple cells are added, the system automatically highlights the cell number and alters its color for easier identification.

If an empty cell is included in a median formula and subsequently populated with a value, the value in cell B9, where the median is calculated, will automatically update to reflect the new value.

Calculating the median by specifying the range

First, we select the cell, where we need to calculate the median. Let’s suppose here the cell number is B9. Then we select the “cell(B9)” and write the following in the cell:

Let’s visualize the above steps in action:

Things to remember

• If we try to find the median of any value (other than numbers) enclosed in double quotes, it will throw a #VALUE! error.

• Empty cells, text, the logical values TRUE and FALSE, and text values are all ignored when used with a range or by typing the cell numbers.