How can I calculate the median value in Google Sheets while ignoring zeros?

By /

To calculate the median value in Google Sheets while ignoring zeros, use the MEDIAN function and filter out any cells containing zero using the IF function. This will allow you to accurately determine the middle value of a set of numbers without including any zero values, which may skew the result. This method is useful when working with data sets that may contain zero values, such as sales data or test scores, where the zeros may not be relevant to the overall calculation of the median.

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:

x

Related terms:

  1. How can Google Sheets calculate the median value while disregarding zeros?
  2. How can I calculate the median value of a range of numbers, and ignore the zeros?
  3. How can I calculate the average in Google Sheets while ignoring zero and blank cells?
  4. How can Power BI calculate an average while ignoring zero values?
  5. How to Calculate Mean, Median & Mode in Google Sheets?
  6. How do I calculate the median in a pivot table using Google Sheets?
  7. Calculate the Median Value of Rows in R?
  8. Calculate the Median Value in MongoDB?
  9. How to Perform a Median IF Function in Google Sheets?
  10. Count Specific Characters in Google Sheets How to count specific characters in google sheets?