Formula Errors

How to Use Excel Average Formula to Ignore Errors, Blanks, and Zeros

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to calculate the average of a specific cell range while excluding any blank cells, zero values, and formula errors.

Product: Excel
Device & OS: not provided
Scenario: Calculating an accurate average in a dataset that contains disruptive error values (like #DIV/0!), empty cells, and zeros that skew the mathematical result.
Observed behavior: The standard AVERAGE function includes zeros in its calculation and returns a total error if the selected range contains any error values, preventing an accurate mean calculation.

Before you start

Identify the exact cell range containing your dataset (e.g., G6:G53). Note that while blank cells are automatically ignored by the standard AVERAGE function, error values and zeros require specialized formula combinations to be bypassed.

Solution 1Recommended

Use AVERAGE, FILTER, and IFERROR to Exclude Zeros and Errors

This is the most comprehensive method to completely exclude error values (like #DIV/0!), blank cells, and zero values from your final average calculation simultaneously.

By combining modern dynamic array functions, you can sanitize your data range before the average is computed. The IFERROR function catches any formula errors and turns them into zeros, and the FILTER function then removes all zeros from the dataset being averaged.

Select a blank destination cell

Click on the empty cell where you want the final calculated average to appear.

Enter the nested formula

Type the formula =AVERAGE(FILTER(IFERROR(G6:G53,0),IFERROR(G6:G53,0)<>0)) into the formula bar. Be sure to replace 'G6:G53' with your actual data range.

Calculate the result

Press the Enter key on your keyboard. The formula will automatically parse the data, ignoring any errors and zeros, and return the clean average.

Formula Breakdown: This formula works by first replacing all #DIV/0! or other errors with a 0. The FILTER function then evaluates the array and only passes values that are not equal to 0 (<>0) into the final AVERAGE function.

Solution 2

Use the AGGREGATE Function to Ignore Errors Only

If you only need to ignore error values and hidden/blank cells, but still want to include valid zero values in your average, the AGGREGATE function is a simpler alternative.

Efficient Data Calculation

Calculate Complex Averages with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions including FILTER, IFERROR, and AGGREGATE. It allows you to clean up messy data, bypass errors, and perform accurate mathematical calculations effortlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your dataset.
2. Input the array formula: Select an empty cell and enter the combined =AVERAGE(FILTER(...)) formula.
3. Get instant results: Press Enter to calculate the accurate average without disruption from errors or zero values.

100% compatible with Microsoft Excel formulas and .xlsx file formats.Supports dynamic arrays for advanced data filtering and calculations.Free, lightweight, and features an intuitive interface familiar to Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate an average in Excel while ignoring only zeros, but no errors exist?

If your dataset contains no errors and you only want to skip zeros, use the AVERAGEIF function. Select a cell and enter =AVERAGEIF(G6:G53, "<>0"). This will average the range while ignoring any cell that contains exactly zero.

Why does my standard AVERAGE formula return a #DIV/0! error?

The #DIV/0! error occurs in an AVERAGE function if the selected range contains no numeric values at all, or if you are averaging cells that themselves contain a #DIV/0! error. Using IFERROR or AGGREGATE helps bypass these underlying errors.

Does the AVERAGE function automatically ignore text and blank cells?

Yes. The standard =AVERAGE() function automatically ignores blank empty cells and cells containing text strings. It only factors in numerical values and zeros. You only need specialized formulas if you need to specifically exclude zeros or formula errors.

Excel & Data Problems