Google Sheets: Calculate Median Value and Ignore Zeros


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:

Google Sheets median ignore zero

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

Leave a Reply

Your email address will not be published. Required fields are marked *