WPS Spreadsheet: Use AVERAGEIFS Without Zeros or #DIV/0! Errors
Question details
Calculate an average for records matching a selected day while excluding zero values and avoiding a division-by-zero error when no positive values match.
- Product
- Microsoft Excel spreadsheet formulas
- Device & OS
- not provided
- Scenario
- The user needs to average values in column F for a day such as Tuesday in column B, while ignoring zero entries.
- Observed behavior
- AVERAGEIFS returns a #DIV/0! error when no positive records meet the selected day and value criteria.
Confirm that the day names in column B are consistent, such as Tuesday, and that the values in F2:F42 are stored as numbers. Adjust the row ranges and day criterion to match your worksheet.
Wrap AVERAGEIFS with IFERROR to return a blank
Use this option when you want to exclude zeros and display a blank cell instead of #DIV/0! when no qualifying values exist.
AVERAGEIFS can apply both the day condition and a greater-than-zero condition. IFERROR handles the error produced when the formula finds no values to average.
Click the cell where you want the calculated average to appear.
Type `=IFERROR(AVERAGEIFS($F$2:$F$42,$B$2:$B$42,"Tuesday",$F$2:$F$42,">0"),"")` and press Enter.
Replace `"Tuesday"` in the formula with another criterion, such as `"Thursday"`, to calculate that day's average.
Test for qualifying values before calculating the average
Use this approach when you prefer to show a custom placeholder, such as a dash, when no positive values are available.
Exclude Zero Values from AVERAGEIFS in WPS Spreadsheet
WPS Spreadsheet supports AVERAGEIFS, IFERROR, and SUMIFS formulas for calculating day-specific averages without including zeros or displaying #DIV/0! when no records qualify.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the day and value columns.
- 2. Choose an output cell: Click the cell where the selected day's average should be displayed.
- 3. Add the protected AVERAGEIFS formula: Enter `=IFERROR(AVERAGEIFS($F$2:$F$42,$B$2:$B$42,"Tuesday",$F$2:$F$42,">0"),"")` in the formula bar.
- 4. Confirm the calculation: Press Enter, then replace Tuesday with another day name whenever you need a different conditional average.
Frequently Asked Questions
Why does AVERAGEIFS return #DIV/0! instead of an average?
AVERAGEIFS returns #DIV/0! when no cells meet all criteria. Wrap it with IFERROR, such as `=IFERROR(AVERAGEIFS(...),"")`, to return a blank instead.
How do I exclude zero values from an AVERAGEIFS calculation?
Add the average range as an additional criteria range and use the criterion `">0"`. For example, use `$F$2:$F$42,">0"` after the day criterion.
Can I use a cell reference instead of typing Tuesday in the formula?
Yes. If cell H2 contains the target day, use `=IFERROR(AVERAGEIFS($F$2:$F$42,$B$2:$B$42,H2,$F$2:$F$42,">0"),"")`.
How can I show a dash when there are no matching values?
Use a dash as the IFERROR result: `=IFERROR(AVERAGEIFS($F$2:$F$42,$B$2:$B$42,"Tuesday",$F$2:$F$42,">0"),"-")`.
