Excel Average Formula for Selected Cells Ignoring Blanks and Zeros
Question details
Calculate the average of selected subtotal cells while excluding zeros and formulas that return empty strings.

- 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.
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.
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.
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.
Select the cell where you want the result and enter =IFERROR(AVERAGEIF(AA1:AA9,"<>0"),""). Press Enter.
Compare the result with the nonzero numeric subtotals shown in AA1:AA9. Update the helper links if the selected subtotal cells change.

Use a dynamic array formula for the selected cells
Use this single-cell option in an Excel version that supports LET, VSTACK, and FILTER.
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. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 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. Enter the average formula: Select a result cell and enter =IFERROR(AVERAGEIF(AA1:AA9,"<>0"),""). Press Enter.
- 4. Review and save: Check the result against the helper cells, then use File > Save As to save the workbook in your preferred format.

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.




