How to Use MEDIAN Function in Excel (4 Suitable Examples)

While working in Excel we may need to find the median value in a range of data that actually reflects the median level of the dataset. We can calculate it easily by using the MEDIAN function in MS Excel. This article will guide you with 4 simple examples and proper illustrations to use the MEDIAN function for finding the median of a data range.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Introduction to the MEDIAN Function

Function Objective:

The MEDIAN function is classified under the statistical function. This function returns the median of a group of numbers. That is actually the number in the middle of a set of numbers.

Syntax:

=MEDIAN (number1, [number2], …)

Arguments Explanation:

Arguments Required/Optional Explanation
number1 Required A number or cell reference that refers to numeric values.
number2 Optional A number or cell reference that refers to numeric values.

4 Examples of Using the MEDIAN Function

Let’s introduce the dataset first. My dataset represents some students’ obtained marks in a Mathematics class test out of 20 marks.


Example 1: Use the Excel MEDIAN Function for Odd Number of Data

Take a look that the number of students in our dataset is 7 which is an odd number. Now we want to find the middle number (median) of the series. So, if we use the MEDIAN function here, we can simply find the middle numbers of values in the series. I’ll show the output in Cell C13.

MEDIAN Function for Odd Number of Data

Steps: 

⏩ Activate Cell C13.

⏩ Type the following formula in it:

=MEDIAN(C5:C11)

⏩ Then just hit the Enter button to get the result.

MEDIAN Function for Odd Number of Data

Now you will see that we have got the middle value of our data-

Read More: How to calculate Average, Median, & Mode in Excel


Example 2: Apply MEDIAN Function for Even Number of Data

Here, I have added a row to make the series even. Now I’ll show how to find the median of this series. As it is an even series so the median will be the average of two middle numbers.

Steps: 

⏩ Write the following formula in Cell C14:

=MEDIAN(C5:C12)

⏩ Press the Enter button to finish.

MEDIAN Function for Even Number of Data

Now you will spot that the median is the average of the values 14 and 15 for even series.

MEDIAN Function for Even Number of Data

Read More: The Different Ways of Counting in Excel


Similar Readings


Example 3: Use MEDIAN Function for Discontinuous Data Range

If the data range is discontinuous then we can apply the MEDIAN function too to find the median. For example, I’ll find the median for the first two and last three numbers of the series then just select the range individually by using a comma in the MEDIAN function. That means, we’ll find the median of the numbers- 10,12,15,18,16.

Steps: 

⏩ In Cell C14 type the following formula:

=MEDIAN(C5:C6,C10:C12)

⏩ Finally, just hit the Enter button.

MEDIAN Function for Discontinuous Data Range

Then you will get the output like the image below-

MEDIAN Function for Discontinuous Data Range


Example 4: Combine MEDIAN with IF for Excluding Zero in a Data Range

If you had zero in your data range and you don’t want to include it in the MEDIAN function then you can use the IF function with the MEDIAN function. The IF function is a logical function that is used to return one value if a condition is true and another value if it’s false. So here, the IF function will exclude zero from the data range by a logical test.

Steps: 

⏩ Write the following formula in Cell C14:

=MEDIAN(IF(C5:C12>0,C5:C12))

⏩ Later, just click the Ctrl+Shift+Enter button.

Find the Median Excluding Zero in a Data Range

Here’s our output excluding zero-

Combine MEDIAN with IF for Excluding Zero in a Data Range

Formula Breakdown:

IF(C5:C12>0,C5:C12)
The IF function will check the values if they are greater than zero or not. If greater then it will return the value otherwise it will return FALSE. So the output will return as-
{10;FALSE;12;15;14;15;18;16}

MEDIAN(IF(C5:C12>0,C5:C12))
Finally, the MEDIAN function will find the median of values extracted from the IF function that returns-
15


Conclusion

I hope all of the methods described above will be good enough to use the MEDIAN function in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo