How to median values ignore zeros or errors in Excel?
In many data analysis tasks in Excel, accurately calculating the median is essential for understanding your dataset’s central tendency. However, sometimes your dataset contains zeros or error values (such as #DIV/0!, #N/A, etc.), which can interfere with a straightforward median calculation. For example, using the standard formula =MEDIAN(range) will include zeros in the calculation and return an error if there are any invalid cells present in the range, potentially leading to misleading results or calculation failures, as illustrated below.
To address this, several solutions can help you calculate the median while excluding zeros or errors, ensuring your analysis is both accurate and robust. These solutions are suitable for various scenarios, such as cleaning survey data, financial reports, or scientific measurements where zeros or errors need to be removed for meaningful results. Below, you'll find practical step-by-step guides for each method available in Excel, ranging from direct formulas to advanced automation techniques.
VBA: Median ignore zeros and errors (UDF)
Power Query: Median after filtering zeros/errors
Median ignore zeros
When your range contains zeros that you do not want to consider in the calculation of the median—such as missing values represented as 0—you can make use of an array formula to exclude zeros. This is especially useful in datasets where zeros are placeholders for unavailable data rather than actual measurements.
Select a cell you want to display the median in (for instance, C2) and enter the following formula:
=MEDIAN(IF(A2:A17<>0,A2:A17))After inputting the formula, instead of just pressing Enter, press Ctrl + Shift + Enter to make it an array formula (you will see curly braces appear around the formula in the formula bar). This ensures only the non-zero values in A2:A17 are considered for the median calculation. See screenshot:
Tips:
- If you are using Excel 365 or Excel 2021 and above, pressing Enter alone is sufficient, thanks to dynamic array support.
- Make sure there is at least one non-zero numerical value in the range, otherwise the formula will return a #NUM! error.
- This solution is ideal for cleaning up survey responses, expense reports, or sales data where zeros should be excluded from the analysis.
Median ignore errors
Error values such as #N/A, #DIV/0!, or #VALUE! can cause the standard median function to return an error, halting your data analysis. To safely calculate the median by excluding these errors, you can use the following array formula.
Select any cell where you would like to display your result, and enter the formula below:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))After entering the formula, press Ctrl + Shift + Enter (unless you are using Excel 365/Excel 2021 or above, which allows dynamic arrays). This formula only includes those values in F2:F17 that are genuine numbers—ignoring any error cells entirely.
Tips and Cautions:
- If all cells are error values, the result will return a #NUM! error—ensure your data includes at least one valid number.
- You can combine exclusion criteria (for example, excluding both zeros and errors) by nesting conditions.
- This formula is especially helpful when working with imported data, survey results, or financial statements that may contain partial or failed calculations.
VBA: Median ignore zeros and errors (UDF)
For scenarios where you frequently need to calculate the median while ignoring both zeros and errors, or require a solution that avoids manually entering array formulas, you can use a custom VBA function (User-Defined Function, UDF). This approach offers extra flexibility because the custom function can encapsulate all ignore-criteria and be used just like any built-in formula, making it suitable for large or frequently-updated datasets.
How to set up the UDF:
- Click the Developer tab in Excel. If it's not available, enable it via File > Options > Customize Ribbon.
- Click Visual Basic to open the VBA editor.
- In the VBA editor, click Insert > Module to create a new module.
- Copy and paste the following code into the module:
Function MedianIgnoreZeroError(rng As Range) As Variant
Dim cell As Range
Dim tempList() As Double
Dim count As Integer
count = 0
On Error Resume Next
xTitleId = "KutoolsforExcel"
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.Value <> 0 And Not IsError(cell.Value) Then
count = count + 1
ReDim Preserve tempList(1 To count)
tempList(count) = cell.Value
End If
End If
Next cell
On Error GoTo 0
If count = 0 Then
MedianIgnoreZeroError = CVErr(xlErrNum)
Else
MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
End If
End FunctionHow to use the UDF:
After returning to Excel, simply enter the formula =MedianIgnoreZeroError(A2:A17) in any cell (replace A2:A17 with your target range). Unlike array formulas, you only need to press Enter—there’s no need for Ctrl + Shift + Enter.
- This method works well for very large datasets, avoids array formula quirks, and can be adapted to ignore other unwanted values by further editing the code.
- If the range contains only zeros or errors, the result will show #NUM!
- If you receive a #NAME? error, check that the VBA macro is correctly installed and that macros are enabled in your Excel settings.
Power Query: Median after filtering zeros/errors
Power Query is a powerful tool in Excel for importing, transforming, and analyzing data—especially when your goal is to clean and preprocess large datasets before performing calculations like the median. With Power Query, you can easily filter out zeros and errors, ensuring that only valid numbers remain in your calculation. This approach is especially beneficial if your source data is regularly updated or imported from external systems.
Steps to use Power Query for calculating median ignoring zeros and errors:
- Select any cell within your data range, then go to the Data tab and click From Table/Range. If your data is not already in table format, Excel will prompt you to create a table—click OK.
- The Power Query Editor window will open. Click the drop-down arrow for the relevant column and uncheck 0 to filter out zero values. (For error filtering, right-click the column header, choose Remove Errors.)
- Once filtered, click Home > Close & Load to send the cleaned data back to your worksheet.
- Now, apply the standard
=MEDIAN()formula to the column with the filtered values only, as the data now excludes all unwanted items.
This method ensures your original data remains unchanged, offers strong repeatability with new or updated data, and is particularly effective for recurring reporting tasks or when working with large or external datasets. Power Query workflows can be refreshed with a single click whenever your source data changes, minimizing manual intervention and risk of errors.
- Power Query is available in Excel 2016 and newer (or as an add-in for Excel 2010 and 2013).
- After transformation, calculations can be performed on the resulting clean data, providing greater reliability for downstream analysis.
If unexpected results occur, double-check your filtering steps in Power Query and confirm there are valid numerical values remaining in your cleaned data.
In summary, whether you prefer using array formulas directly, creating a custom VBA solution for automation, or leveraging Power Query for larger workflow automation, Excel offers multiple practical options for calculating the median while ignoring zeros or errors. Choose the method that best fits your dataset size, update frequency, and workflow preferences for reliable and accurate outputs.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in