Formula Errors

How to Calculate Excel Average Excluding Zero Values

Huma Ashraf Ch Oct 22, 2026 868 views

Question details

Calculate the average of a dataset while ignoring zero values to prevent them from artificially lowering the final mathematical result.

How to Calculate Excel Average Excluding Zero Values

Product: Excel
Device & OS: not provided
Scenario: A user needs to find the true average of only the positive or non-zero numbers within a dataset, such as test scores or sales figures, where zero represents missing or inapplicable data.
Observed behavior: Using the standard AVERAGE function includes the zeros in the mathematical division, which inappropriately lowers the calculated average.

Before you start

Identify whether the cells you want to average are located in a continuous range (e.g., D6 to J6) or scattered across non-contiguous cells, as this dictates the exact formula you need to apply.

Solution 1Recommended

Use AVERAGEIF for Contiguous Ranges

This is the most straightforward method when all the numbers you want to average are located next to each other in a single row or column.

The AVERAGEIF function evaluates a contiguous block of cells and averages only those that meet a specific mathematical criterion. By setting the criterion to greater than zero (">0"), you ensure that zeros do not affect your final calculation.

Select the destination cell

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

Enter the AVERAGEIF formula

Type the formula =AVERAGEIF(D6:J6, ">0", D6:J6) into the formula bar. Replace 'D6:J6' with the actual range containing your data.

Calculate the result

Press the Enter key. The cell will now display the average of all numbers greater than zero within the selected range.

Syntax Tip: If you want to exclude exactly zero but include negative numbers, change the criterion to "<>0" instead of ">0".

Solution 2

Use an Array Formula for Non-Contiguous Cells

Use this method if the values you need to average are separated by other data columns or rows (e.g., D6, F6, H6, and J6).

Solution 3

Use FILTER and CHOOSECOLS in Modern Excel

A dynamic array alternative for newer Excel versions to extract specific columns and filter out zeros simultaneously.

Advanced Spreadsheet Capabilities

Calculate Complex Averages Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical functions like AVERAGEIF and complex array formulas, allowing you to exclude zero values from your calculations effortlessly. It provides a smooth, fast data analysis experience that perfectly matches your workflow.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your dataset.
2. Select a calculation cell: Click on any empty cell where you want the final average to be displayed.
3. Apply the function: Type =AVERAGEIF(D6:J6, ">0") and press Enter to instantly calculate your adjusted average without zero values.

Easily apply AVERAGEIF to exclude zeros without formatting issues100% compatible with Microsoft Excel formulas and .xlsx filesFree, lightweight application that runs smoothly on any deviceBuilt-in data analysis tools for advanced statistical processing

microsoft office alternative - wps office

Frequently Asked Questions

Can I exclude both zeros and negative numbers from the average?

Yes, by using the ">0" criteria in the AVERAGEIF function, you are inherently excluding both zero and any negative numbers. Only numbers strictly greater than zero will be factored into the final average.

Will blank cells affect my average calculation?

No. The standard AVERAGE and AVERAGEIF functions in Excel and WPS Spreadsheet automatically ignore empty or completely blank cells. You only need explicit criteria to ignore cells that actually contain the number zero.

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

The #DIV/0! error occurs when no cells in your specified range meet the condition. For example, if all the numbers in the range are zero or negative, the formula attempts to divide the sum by zero eligible cells. Ensure at least one cell meets the ">0" criteria.

Excel & Data Problems