Table of Contents
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.