How to Hide #DIV/0! Errors and Return Blank Cells in WPS Office Spreadsheet
Question details
The user needs the averaging formula in Z8 to return blank instead of zero when a range contains #DIV/0! errors, and the dependent IFS formula in AA8 to remain blank when no valid average exists.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is working with a worksheet where cells S8:Y8 may contain #DIV/0! errors, and Z8 and AA8 should return a blank cell until at least one valid number is available for the average and grade.
- Observed behavior
- The average may return zero, or the dependent IFS formula displays an unwanted result when the source range contains only #DIV/0! errors.
Open your workbook in WPS Office Spreadsheet and confirm that the formulas in S8:Y8 can produce #DIV/0! errors when a divisor is blank or zero. Identify cells Z8 and AA8 where the average and dependent grade result should appear.
Use IFERROR with AVERAGEIFS in Z8 and guard the AA8 IFS formula
Use this solution when you want to keep the error-containing source cells unchanged and make only the summary formulas return blank.
AVERAGEIFS can ignore cells that return #DIV/0! by using the criteria "<>#DIV/0!", while IFERROR returns an empty string when no numeric values remain. The dependent IFS in AA8 then checks for an empty Z8 before evaluating the grade bands.
Select cell Z8 and enter =IFERROR(AVERAGEIFS(S8:Y8,S8:Y8,"<>#DIV/0!"),""). Press Enter. Z8 now returns a blank cell when no valid numbers are present.
Select cell AA8 and enter =IF(Z8="","",IFS(Z8>=3.5,AA$3,Z8>=2.5,AA$4,Z8>=1.5,AA$5,Z8>0,AA$6)). Press Enter. This keeps AA8 blank if Z8 is blank.
Try entering valid numbers in S8:Y8, and then test a case where the range contains only #DIV/0! errors. Both Z8 and AA8 should remain blank until at least one valid number exists.
Hide #DIV/0! at the source before averaging
Use this alternative when you prefer each source formula to return blank instead of an error, so the range contains only numbers or blanks.
Use WPS Office Spreadsheet Formulas to Hide #DIV/0! Errors and Return Blank Cells
WPS Office Spreadsheet supports IFERROR, AVERAGEIFS, and IFS, so you can make the Z8 average and AA8 dependent formula return blank when a range contains #DIV/0! errors or no valid numbers. Apply the two formulas directly in WPS to keep your grading summary clean.
- 1. Open your workbook in WPS Office Spreadsheet: Launch WPS Office, open the workbook containing the #DIV/0! errors, and select the sheet with cells S8:Y8, Z8, and AA8.
- 2. Apply the averaging formula in Z8: Click cell Z8 and enter =IFERROR(AVERAGEIFS(S8:Y8,S8:Y8,"<>#DIV/0!"),""), then press Enter.
- 3. Apply the dependent IFS formula in AA8: Click cell AA8 and enter =IF(Z8="","",IFS(Z8>=3.5,AA$3,Z8>=2.5,AA$4,Z8>=1.5,AA$5,Z8>0,AA$6)), then press Enter.
- 4. Test the blank behavior: Fill or clear S8:Y8 to compare valid-number and error-only cases, and confirm that Z8 and AA8 remain blank until a valid average exists.
Frequently Asked Questions
Why does AVERAGE return zero instead of blank when the range has only #DIV/0! errors?
AVERAGE cannot ignore errors directly. You can filter them with AVERAGEIFS using the criteria "<>#DIV/0!" and wrap the result in IFERROR to return a blank cell, or wrap the source formulas in IFERROR.
How does the formula =IFERROR(AVERAGEIFS(S8:Y8,S8:Y8,"<>#DIV/0!"),"") work?
AVERAGEIFS averages only the cells in S8:Y8 that do not equal #DIV/0!. IFERROR then returns an empty string if no numbers match the condition.
Can I use these formulas in WPS Office Spreadsheet?
Yes. WPS Office Spreadsheet supports IFERROR, AVERAGEIFS, and IFS, and it saves .xlsx files so the same formulas work in WPS Office and Microsoft Excel.
How do I keep AA8 blank when Z8 is blank?
Start the AA8 formula with =IF(Z8="","",IFS(...)). This stops the IFS logic from running until Z8 contains a valid number.
What should I do if my source cells already contain the #DIV/0! error?
You can either keep the source cells unchanged and use the AVERAGEIFS criteria in Z8, or wrap each source formula in IFERROR to return "" before using a simpler AVERAGE formula.
