Function Problems

How to Use Excel AVERAGE Formula Excluding Errors, Zeros, and Specific Text

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to calculate the average of a column of values while ignoring error values, zeros, and rows containing a specific text string in another column.

Product: Spreadsheet
Device & OS: not provided
Scenario: Calculating a conditional average that dynamically filters out unwanted data points, such as errors, zeros, and specific text criteria across different columns.
Observed behavior: Standard AVERAGE or AVERAGEIFS functions either include zero values, skewing the result, or return an error if the source range contains error codes like #DIV/0! or #N/A.

Before you start

Ensure your spreadsheet software supports dynamic array functions like FILTER. Verify the exact text you want to exclude and the exact range of your data before writing the formula.

Solution 1Recommended

Use FILTER and IFERROR Functions to Calculate Average

Combine the AVERAGE, FILTER, and IFERROR functions to dynamically exclude zero values, error codes, and unwanted text rows from your calculation.

This method uses the FILTER function to extract only the valid numbers from your target column by applying multiple exclusion criteria. The IFERROR function is used first to handle any existing formula errors in the range, treating them as zeros so they can be seamlessly filtered out by the subsequent conditions.

Select the result cell

Click on the cell where you want the final average calculation result to appear.

Enter the nested dynamic array formula

Type the formula =AVERAGE(FILTER(IFERROR(J6:J53,0),((IFERROR(J6:J53,0)<>0)*(A6:A53<>"t")))) into the formula bar. Replace 'J6:J53' with the column range you want to average, 'A6:A53' with your criteria column range, and 't' with the specific text you want to exclude.

Execute the formula

Press Enter. The formula will evaluate the arrays, exclude the errors, skip the zero values, and ignore rows where your criteria column matches the specified text, returning the accurate average.

Understanding the formula logic: The multiplication symbol (*) acts as an AND operator in dynamic array formulas. This means both conditions (value is not zero AND text is not 't') must be true for the number to be included in the final average calculation.

Solve complex formulas easily with WPS Office

Calculate Conditional Averages Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and IFERROR, allowing you to manage complex data calculations with ease. It is highly compatible with Microsoft Excel formulas, ensuring your data workflows remain completely uninterrupted.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your Excel workbook (.xlsx or .xls) containing the data.
2. Apply the conditional average formula: Select an empty cell and enter the combined AVERAGE and FILTER formula just as you would in standard Excel.
3. Calculate and format: Press Enter to get your result instantly, and use the formatting toolbar to adjust the decimal places of your average as needed.

Seamless compatibility with Microsoft Excel formulas and .xlsx file formats.Full support for advanced dynamic array functions like FILTER.Free, lightweight, and fast spreadsheet processing.Built-in error checking and intuitive formula evaluation tools.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use AVERAGEIFS instead of FILTER for this calculation?

AVERAGEIFS can successfully exclude zeros and specific text strings, but it fails if the average range contains underlying errors (like #DIV/0!). Using FILTER combined with IFERROR is a more robust solution for ranges that already contain formula errors.

Why does my formula return a #CALC! error?

The #CALC! error occurs when the FILTER function finds no data that meets your criteria (e.g., all values in the range are zeros, errors, or match the excluded text). To handle this gracefully, wrap your entire formula in an IFERROR function, like =IFERROR(AVERAGE(...), 0).

Will this array formula work in older versions of Excel or WPS Office?

The FILTER function is a dynamic array function available only in newer versions (like Microsoft 365, Excel 2021, and updated versions of WPS Office). If you are using an older version, you will need to rely on a complex Ctrl+Shift+Enter array formula combining AVERAGE, IF, and ISERROR.

Excel & Data Problems