How can Google Sheets calculate the median value while disregarding zeros?

By /

Google Sheets has the ability to calculate the median value while disregarding zeros by using the MEDIAN function. This function automatically ignores cells with a value of zero when calculating the median, allowing for a more accurate representation of the data. This is useful in situations where zeros may skew the results, such as in a dataset with a wide range of values. By disregarding zeros, Google Sheets can provide a more precise median value that better reflects the central tendency of the data.

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 I calculate the median value of a range of numbers, and ignore the zeros?
  2. How to Calculate Mean, Median & Mode in Google Sheets?
  3. Count Specific Characters in Google Sheets How to count specific characters in google sheets?
  4. Calculate the Median Value of Rows in R?
  5. Calculate the Median Value in MongoDB?
  6. How to Perform a Median IF Function in Google Sheets?
  7. Google Sheets: Find First Occurrence of Value in Column
  8. How to Highlight Highest Value in Google Sheets?
  9. How to Select a Random Value From a List in Google Sheets
  10. How to Count Rows with Value in Google Sheets (3 Examples)