Fix #DIV/0! Errors in an Excel AVERAGE Formula
Question details
Calculate an average for each block and display a blank when the block contains no numeric values.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook has several blocks with varying numbers of numeric entries and a grand average based on the block averages.
- Observed behavior
- AVERAGE returns #DIV/0! when a block has no numeric values; the desired result is a blank cell.
Identify the cells in each block and the cells that contain the block averages. Confirm that zero should count as a numeric value, while blank cells and text should be ignored.
Wrap AVERAGE in IFERROR to return a blank
Use this approach to calculate an average when numbers exist and hide errors when they do not.
Select the cell where the first block average should appear, enter =IFERROR(AVERAGE(L20:L32),""), and press Enter.
Enter the same formula in each block's average cell, replacing L20:L32 with that block's range.
In the grand-average cell, enter =IFERROR(AVERAGE(L34,L68,L102,L136,L170,L204,L238,L272,L306),"") to return a blank if none of the referenced cells contains a numeric result.

Check for numbers with COUNT before averaging
Use this formula when you want to avoid the empty-block error without hiding other calculation errors.
Keep Empty Block Averages Blank in WPS Spreadsheets
Use the same IFERROR and AVERAGE formula in WPS Spreadsheets to calculate block averages without displaying an error for empty blocks.
- 1. Open the workbook: Launch WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Enter the formula: Select a block-average cell and enter =IFERROR(AVERAGE(L20:L32),"") in the formula bar, adjusting the range as needed.
- 3. Repeat and save: Enter the formula for the other blocks, then select Save to keep the changes.

Frequently Asked Questions
Why does AVERAGE return #DIV/0! for an empty block?
AVERAGE has no numeric values to divide by. Use =IFERROR(AVERAGE(L20:L32),"") to display a blank instead.
Do text and blank cells affect the block average?
AVERAGE ignores text and blank cells in a referenced range. Numeric zeros are included in the calculation.
How can I keep other formula errors visible?
Use =IF(COUNT(L20:L32)=0,"",AVERAGE(L20:L32)). When the block contains at least one number, errors encountered by AVERAGE can still appear.
How do I prevent a #DIV/0! error in the grand average?
Wrap the formula that references the block-average cells in IFERROR, for example, =IFERROR(AVERAGE(L34,L68,L102),""). Include the remaining block-average cells in the AVERAGE arguments.




