How to Calculate the Median in Different Cases in Excel

The median is the number in the middle of a range of data by order, half of the data in this range is larger than it, and half is smaller than it. Sometimes we want to calculate the median for a range of data. This article will introduce you the formula to calculate the median for a range in some different cases in excel.

Calculate the Median for A Range Including Zero Number


See example below:

Calculate the Median 1

Step 1: In any blank cell, enter the formula =MEDIAN(A1:B3).

Calculate the Median 2

Step 2: Click Enter to get the result. Verify that zero number is included in calculating.

Calculate the Median 3

Calculate the Median for A Range Excluding Zero Number


We still use above example to calculate the median, but this time zero numbers will be ignored in calculating.

Step 1: In any blank cell, enter the formula =MEDIAN(IF(A1:B3>0,A1:B3)).

Calculate the Median 4

Step 2: Press ‘Ctrl + Shift + Enter’ to get the result. Verify that zero number is excluded in calculating.

Calculate the Median 5

Calculate the Median for Multiple Ranges in Excel


See the table below:

Calculate the Median 6

You can see that there are two ranges of data in two tables. So, if we want to calculate the median for the two ranges, you can follow below steps to calculate the median.

Step 1: In any blank cell, enter the formula =MEDIAN(A1:B3,A5:B7).

Calculate the Median 7

If there are multiple ranges exist, we can use a comma to separate different ranges.

Step 2: Click Enter to get the result.

Calculate the Median 8

Related Functions


  • Excel MEDIAN function
    The Excel AVERAGE function returns the median of the given numbers. And the median is the number in the middle of a list of supplied numbers.The syntax of the MEDIAN function is as below:=MEDIAN (number1, [number2], …)….
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

You might also like:

Sidebar