ExcelInsider Team
Real world datasets often include zeros that might skew the results. For instance, when analyzing sales numbers, production quantities, or experimental measurements, having zeros can lead to inaccurate measurements of central tendency. This article explains what is median if not zero in Excel and how to determine the median while ignoring zeros in Excel. In addition, you will also learn to calculate the median excluding zeros and errors.
➤ Median if not zero means calculating the median of values and ignoring all the zero values. In this article, we’ll learn about Excel median if not zero and how to calculate the median excluding zero by combining the MEDIAN, IF, and FILTER functions.
➤ MEDIAN and IF: =MEDIAN(IF(cell_range<>0,cell_range))
➤ MEDIAN and FILTER: =MEDIAN(FILTER(cell_range,cell_range<>0))
➤ Median if not zeros and errors excludes zeros and errors to calculate the correct median.
➤ MEDIAN, IF, & ISNUMBER: =MEDIAN(IF(ISNUMBER(cell_range),IF(cell_range<>0,cell_range)))
Combining MEDIAN and IF Functions for Excel Median If Not Zero
This is the classic formula that performs the conditional check using the IF function to exclude zeros. It is suitable for all versions of Excel; however, note that this is an array formula, so for older versions of Excel, you must press a combination of keys to evaluate the array formula; otherwise, you may encounter an error.
The following sales dataset contains the Region, Product, Sales Rep, Unit Sold, and Revenue ($) from columns A through E.
Suppose you are analyzing this data and want to calculate the median unit sold and revenue. If you use only the MEDIAN function, you will get a false median since there are a few zero entries. So you will need the help of other functions to calculate the median while ignoring the zeros.
Steps:
➤ Go to the B13 cell and enter the formula below. For Excel 2019 or earlier versions, press the Ctrl + Shift + Enter keys. On Excel 365 or later versions, just press Enter .
=MEDIAN(IF(D2:D11<>0,D2:D11))
The IF function checks whether the values in the D2:D11 range are not equal to zero and returns those values that satisfy the condition. The MEDIAN function calculates the median units sold.
Excel Median If Not Zero with MEDIAN and FILTER Functions
This is a modern approach to dynamically exclude zeros before calculating the median by combining the FILTER and MEDIAN functions. However, only Excel 365 and later versions support this straightforward alternative.
Steps:
➤ Move to the B13 cell and use the FILTER and MEDIAN functions.
=MEDIAN(FILTER(E2:E11,E2:E11<>0))
With the help of the not equal operator (<>), the FILTER function returns the values that satisfy the non zero condition for the Revenue column. The MEDIAN function calculates the median Revenue.
Calculating Median Excluding Zeros and Errors in Excel
If a dataset contains both zeros and error values, directly calculating the median can produce inaccurate results or break the formula. To ensure the correct calculation of the median, it is necessary to filter out both zeros and errors.
The previous dataset now contains zeros and errors. Now, we will calculate the median excluding them.
Using the IF, ISNUMBER, and FILTER functions, we can exclude the unwanted values and estimate the correct median.
Detecting Non Zero Values and Excluding Them While Calculating Median
By combining the MEDIAN, IF, and ISNUMBER functions, we can reliably obtain the result without having to worry about zeros or errors. The combination of IF and ISNUMBER checks whether the values in the range are numbers and non zero.
Steps:
➤ Move to the B13 cell and use the formula below to obtain the median unit sold.
=MEDIAN(IF(ISNUMBER(D2:D11),IF(D2:D11<>0,D2:D11)))
Frequently Asked Questions
How to calculate median in Excel without zeros?
=MEDIAN(IF(cell_reference<>0,cell_reference)). Press Ctrl + Shift + Enter .
How do you average if not 0 in Excel?
=AVERAGEIF(cell_reference, "<>0")
How to calculate median ignoring errors?
=MEDIAN(IF(ISNUMBER(cell_reference),cell_reference)). Press Ctrl + Shift + Enter .
What does <> mean in Excel?
In Excel, <> is not equal operator.
Are blanks and text automatically ignored by MEDIAN function?
Yes, the MEDIAN function ignores blanks and texts. However, any error values can break the formula. Using the ISNUMBER function excludes error values.
Wrapping Up
In this tutorial, we learned about the conditional median to exclude zeros and how to calculate the Excel median if it is not zero using functions. In addition, we also learned to find the conditional median excluding both zeros and errors. Feel free to download the practice file and share your thoughts and suggestions.
