You can use the following formula in Excel to calculate the median value of a particular range and ignore any values equal to zero:

=MEDIAN(IF(B2:B10<>0,B2:B10))

This particular example will calculate the median value in the range **B2:B10** and ignore any values equal to zero when calculating the median.

The following example shows how to use this syntax in practice.

**Example: Calculate Median Value and Ignore Zeros in Excel**

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we use the following formula to calculate the median value in the points column:

**=MEDIAN(B2:B10)
**

The following screenshot shows how to use this formula in practice:

By default, Excel considers every value in the range when calculating the median.

Thus, the **MEDIAN** formula arranged each value in the points column from smallest to largest and chose the middle value as the median:

Points values: 0, 0, 13, 14, **18**, 22, 24, 28, 29

However, we can use the following formula instead to calculate the median value in the points column and ignore all values equal to zero:

=MEDIAN(IF(B2:B10<>0,B2:B10))

The following screenshot shows how to use this formula in practice:

This particular formula calculated the median value in the points column and ignored all points values equal to zero.

Thus, the **MEDIAN** formula arranged each value in the points column from smallest to largest (excluding the zeros) and chose the middle value as the median:

Points values: 13, 14, 18, **22**, 24, 28, 29

When ignoring values equal to zero, the median value turns out to be **22**.

**Additional Resources**

The following tutorials explain how to perform other common operations in Excel:

How to Calculate the Median by Group in Excel

How to Calculate Median of Filtered Rows in Excel

How to Calculate the Median in a Pivot Table in Excel