Calculation Issues

Excel Average Formula for Selected Cells Ignoring Blanks and Zeros

Emma BrownEmma Brown Nov 20, 2026 868 views

Question details

Calculate the average of selected subtotal cells while excluding zeros and formulas that return empty strings.

Average Selected Excel Cells While Ignoring Blanks and Zeros
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has subtotal values in separate cells from N34 through N306, and only those specific cells should contribute to the average.
Observed behavior
A previous array formula returned a different result from the manually calculated average. The user wants to include only the listed subtotal cells.
Before you start

Confirm the subtotal cells you want to include and choose an unused area for a helper range. The examples use N34, N68, N102, N136, N170, N204, N238, N272, and N306.

Solution 1Recommended

Average the subtotal cells using a helper range

Use this maintainable method when the subtotal cells are noncontiguous or your Excel version does not support VSTACK.

1
Link the selected cells

In an unused range such as AA1:AA9, enter these formulas in order, one per cell: =N34, =N68, =N102, =N136, =N170, =N204, =N238, =N272, and =N306.

2
Calculate the average

Select the cell where you want the result and enter =IFERROR(AVERAGEIF(AA1:AA9,"<>0"),""). Press Enter.

3
Check the result

Compare the result with the nonzero numeric subtotals shown in AA1:AA9. Update the helper links if the selected subtotal cells change.

Average the subtotal cells using a helper range
When the result is blank: The formula returns an empty string if there are no qualifying numbers. IFERROR can also hide other errors, so check the source cells if you expected a result.
Calculate averages in WPS Spreadsheet

Average Selected Subtotals in WPS Spreadsheet

Use a helper range and AVERAGEIF in WPS Spreadsheet to average selected subtotal cells without including zeros or blank results.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Create a helper range: In unused cells AA1:AA9, enter =N34, =N68, =N102, =N136, =N170, =N204, =N238, =N272, and =N306, one formula per cell.
  3. 3. Enter the average formula: Select a result cell and enter =IFERROR(AVERAGEIF(AA1:AA9,"<>0"),""). Press Enter.
  4. 4. Review and save: Check the result against the helper cells, then use File > Save As to save the workbook in your preferred format.
Exclude zero values and blank results from a subtotal averageOpen and edit Microsoft Excel XLSX and XLS filesKeep noncontiguous subtotal references easy to reviewUse familiar spreadsheet formulas and controls
QA img-9

Frequently Asked Questions

Why use a helper range for noncontiguous cells?

AVERAGEIF expects a range for its criteria. Linking the selected cells in AA1:AA9 creates a contiguous range that is easy to inspect and update.

Does the formula exclude cells containing a formula that returns an empty string?

Yes. In the helper-range method, AVERAGEIF averages qualifying numeric values; text results such as an empty string are not averaged. The <>0 criterion excludes numeric zeros.

What does 34 mean in a MOD and ROW formula?

In MOD(ROW(N34:N306),34)=0, 34 selects rows whose row numbers are divisible by 34, such as 34, 68, and 102. Check the range endpoints carefully: N32:N158 cannot include subtotals below row 158.

Why does the average formula return a blank?

IFERROR returns an empty string if no qualifying numeric values are found or if another error occurs. Check the helper cells and source formulas for unexpected errors.