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

=ARRAYFORMULA(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 Google Sheets**

Suppose we have the following dataset in Google Sheets that contains information about points scored by basketball players on various teams:

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, Google Sheets 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:

=ARRAYFORMULA(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 tasks in Google Sheets:

Google Sheets: How to Calculate Median in a Pivot Table

Google Sheets: How to Average Across Multiple Sheets

Google Sheets: How to Create a Summary Table