WPS Spreadsheet: Return 0 Instead of #DIV/0! in AVERAGEIF
Question details
The user needs AVERAGEIF to return 0 instead of #DIV/0! when the source range has no positive values.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet calculates an average from cells in BW73:CA73, and the result should remain zero until a value greater than zero appears.
- Observed behavior
- AVERAGEIF returns #DIV/0! when all cells in the source range are zero, blank, or otherwise fail the positive-value criterion.
Confirm that BW73:CA73 is the intended source range and that only values greater than zero should be included in the average. Check whether the cells contain numeric zeros, blanks, or text before applying the formula.
Wrap AVERAGEIF with IFERROR to return zero
Use this solution when no qualifying values should produce 0 instead of the #DIV/0! error.
AVERAGEIF returns #DIV/0! when no cells meet the criteria because there is nothing to average. IFERROR replaces that error with 0 while preserving the average whenever one or more positive values are available.
Open the worksheet in WPS Spreadsheet and click the cell where the average result should appear.
Type =IFERROR(AVERAGEIF(BW73:CA73,">0"),0) in the formula bar or directly in the selected cell.
Press Enter. The cell returns the average of values greater than zero, or 0 when no qualifying value exists.
Enter a value greater than zero in BW73:CA73 and confirm that the formula updates from 0 to the average of the positive values.
Handle AVERAGEIF errors easily in WPS Spreadsheet
WPS Spreadsheet lets you combine AVERAGEIF and IFERROR to control formula output, keep worksheets readable, and calculate positive-value averages without displaying #DIV/0!.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing the AVERAGEIF formula.
- 2. Select the formula cell: Click the cell that currently displays #DIV/0! or where the corrected result should appear.
- 3. Replace the formula: Enter =IFERROR(AVERAGEIF(BW73:CA73,">0"),0) in the formula bar.
- 4. Apply and verify: Press Enter, then test the result with no positive values and with at least one positive value in the source range.
Frequently Asked Questions
Why does AVERAGEIF return #DIV/0!?
AVERAGEIF returns #DIV/0! when no cells meet its criteria, because there are no qualifying numbers to average. This occurs when the source range contains only zeros, blanks, or nonmatching values.
What formula returns 0 when AVERAGEIF finds no positive values?
Use =IFERROR(AVERAGEIF(BW73:CA73,">0"),0). It returns the average of values greater than zero and substitutes 0 when no values meet the criterion.
Will the formula update when a positive value is added?
Yes. When a value greater than zero is entered anywhere in BW73:CA73, WPS Spreadsheet recalculates the formula and returns the average of all qualifying positive values.
Can I use a different error replacement value?
Yes. Replace the final 0 in IFERROR with another number or text, such as =IFERROR(AVERAGEIF(BW73:CA73,">0"),"No data"). Use 0 when the worksheet requires a numeric result for later calculations.
