Function Problems

How to Average Noncontiguous Excel Ranges While Excluding Zeros and Blanks

Camila Milosovich Oct 10, 2026 869 views

Question details

The user needs an Excel formula to calculate the average of selected, non-adjacent cells that follow a repeating pattern across a row, ensuring that both blank cells and zero values are excluded from the calculation.

How to Average Noncontiguous Excel Ranges While Excluding Zeros and Blanks

Product: Excel
Device & OS: not provided
Scenario: Calculating accurate averages for periodic data (like every 7th or 9th column) in a wide dataset without allowing zero values or empty cells to artificially lower the average.
Observed behavior: Standard AVERAGE functions include zero values, and manually selecting individual cells using simple division formulas becomes tedious and error-prone for repeating patterns.

Before you start

Ensure your dataset either has a consistent repeating column pattern (e.g., every 7th column) or a well-defined header row that clearly identifies the columns you want to include in the average.

Solution 1Recommended

Use AVERAGEIFS with a Header Row Criterion

If your target columns share a specific header name (like a month label or a 'Total' column), AVERAGEIFS is the most straightforward way to exclude zeros and skip irrelevant columns.

The AVERAGEIFS function evaluates multiple conditions. By setting one condition to match your specific column header and another condition to be greater than zero, you can easily average only the relevant, non-zero data. Blank cells are automatically ignored by AVERAGEIFS.

Identify your data and header ranges

Determine the row containing your headers (e.g., H1:AB1) and the row containing the data you want to average (e.g., H2:AB2).

Enter the AVERAGEIFS formula

Select an empty cell and enter the formula: =AVERAGEIFS($H$2:$AB$2, $H$1:$AB$1, A$1, $H2:$AB2, ">0"). In this example, A$1 contains the target header name you are looking for.

Press Enter and apply to other rows

Hit Enter to get the result. You can then drag the fill handle down to apply this formula to subsequent rows in your dataset.

Use AVERAGEIFS with a Header Row Criterion

Excluding negative values: The criterion ">0" successfully excludes both zeros and any negative numbers. If you need to include negative numbers but just exclude absolute zeros, change the criterion to "<>0".

Solution 2

Use INDEX and SEQUENCE for Pattern-Based Cell Selection

For datasets that lack a uniform header but follow a strict repeating interval (e.g., every 7th or 9th column), dynamic array formulas can mathematically extract and average the correct cells.

Easily Calculate Complex Averages in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions and multi-criteria averaging formulas like AVERAGEIFS and SEQUENCE. You can seamlessly process noncontiguous datasets and filter out zeroes without worrying about compatibility issues.

1. Open your dataset in WPS: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx).
2. Select the destination cell: Click on the cell where you want your calculated average to be displayed.
3. Insert the AVERAGEIFS function: Go to the Formulas tab, click 'Insert Function', search for AVERAGEIFS, and easily map your header ranges and the ">0" criteria using the visual dialog box.
4. Get instant results: Click OK to instantly calculate your average, successfully excluding zeros and blank cells.

100% compatible with Microsoft Excel formulas, including AVERAGEIFS and dynamic arrays.Free, lightweight, and fast performance even on large datasets.Intuitive Function Wizard helps you build and troubleshoot complex formulas step-by-step.

Frequently Asked Questions

Does the standard AVERAGE function ignore blank cells?

Yes, the standard AVERAGE function in Excel and WPS Spreadsheet automatically ignores completely blank cells. However, it will include cells containing the number zero, which can bring your overall average down.

Can I manually select noncontiguous cells for an AVERAGE formula?

Yes, you can type =AVERAGE( and then hold down the Ctrl key while clicking the specific cells you want to include (e.g., =AVERAGE(A1, C1, E1)). However, this method will still include zero values unless combined with more complex IF statements.

Why am I getting a #DIV/0! error when excluding zeros?

The #DIV/0! error occurs if every single cell in your selected range is either empty or contains a zero, leaving the formula with zero valid numbers to divide by. You can wrap your formula in =IFERROR(your_formula, "") to display a blank cell instead of the error.

How do I exclude text or errors from my average calculation?

Standard AVERAGE and AVERAGEIFS functions naturally ignore cells containing text. To ignore error values like #N/A or #VALUE!, you can use the AGGREGATE function, specifically =AGGREGATE(1, 6, your_range).

Excel & Data Problems