Function Problems

How to Calculate an Average in Excel Ignoring Zero and Text Values

Jason Miller Oct 22, 2026 868 views

Question details

The user needs to calculate the average of a specific cell range while including only numeric values greater than zero, ensuring both zeroes and text entries are ignored in the calculation.

How to Calculate an Average in Excel While Ignoring Zero and Text Values

Product: Excel
Device & OS: not provided
Scenario: Performing statistical calculations on a dataset where zero values and text labels could skew the true mathematical average of valid positive numbers.
Observed behavior: The user seeks a specific formula that yields a clean average by bypassing any zeroes and non-numeric characters present in the designated range.

Before you start

Ensure your target data range does not contain hidden error values (like #DIV/0! or #VALUE!), as these will cause the averaging formula to return an error even if criteria are set.

Solution 1Recommended

Use the AVERAGEIFS Function

The AVERAGEIFS function is the most efficient and modern method to calculate an average based on specific criteria, naturally ignoring text and bypassing zeroes when set to values greater than zero.

This formula applies a condition ">0" to the range. By default, Excel's mathematical functions ignore text strings, so applying the strictly positive criteria ensures zero values are skipped as well.

Select the destination cell

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

Input the AVERAGEIFS formula

Type the formula =AVERAGEIFS(B2:F2, B2:F2, ">0") into the formula bar. Replace B2:F2 with your actual data range.

Execute the calculation

Press the Enter key. The cell will now display the average of only the positive numbers within the range.

Automatic Text Exclusion: You do not need an additional condition to exclude text; the AVERAGEIFS function automatically omits cells containing text from the calculation.

Solution 2

Use an Array Formula (For Older Excel Versions)

If you are using an older version of Excel that does not support the AVERAGEIFS function, you can combine the AVERAGE and IF functions into an array formula.

Advanced Data Processing

Calculate Complex Averages Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced statistical functions like AVERAGEIFS and array formulas. You can process complex datasets, filter out unwanted zeroes or text, and obtain accurate results instantly without worrying about compatibility issues.

1. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing the data you want to average.
2. Enter the formula: Click on your target cell and type =AVERAGEIFS(B2:F2, B2:F2, ">0").
3. Get the result: Press Enter to instantly calculate the average while ignoring all zeroes and text values.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsNative support for AVERAGEIFS, AVERAGEIF, and legacy array functionsFree, lightweight, and fast performance for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Does the standard AVERAGE function ignore text automatically?

Yes, the standard =AVERAGE() function automatically ignores cells containing text and empty cells. However, it includes zero values in its calculation, which reduces the final average. This is why a conditional function like AVERAGEIFS is needed when zeroes must also be skipped.

How can I calculate an average that ignores zeroes but includes negative numbers?

If your dataset has valid negative numbers and you strictly want to exclude zeroes, use the not-equal-to operator. The formula should be written as =AVERAGEIF(B2:F2, "<>0").

What if my range contains error values that break the AVERAGEIFS formula?

If cells contain errors like #DIV/0! or #N/A, AVERAGEIFS will return an error. To bypass errors, zeroes, and text, use the AGGREGATE function. For example, =AGGREGATE(1, 6, B2:F2/(B2:F2>0)) entered as an array formula will calculate the average while ignoring errors and non-positive numbers.

Excel & Data Problems