Average Q1 and Q3 Values in Excel Without a #DIV/0! Error
Question details
Calculate a combined average of Q1 and Q3 values while excluding blanks and avoiding a division-by-zero error.

- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- Quarter labels are in column G and corresponding values are in column H. The user wants one average for rows labeled Q1 or Q3.
- Observed behavior
- An average formula can return #DIV/0! when no matching numeric values are available.
Check that G2:G13 contains the quarter labels and H2:H13 contains the values to average. Adjust both ranges to cover the same rows in your worksheet.
Use IFERROR with an Excel array formula
Use this Microsoft 365 formula to calculate one average across matching Q1 and Q3 rows and show a message when no numeric values match.
Click an empty cell where you want the combined average to appear.
In the formula bar, enter =IFERROR(AVERAGE(IF(((G2:G13="Q1")+(G2:G13="Q3"))*(H2:H13<>""),H2:H13)),"No matching numeric values") and press Enter.
If your labels or values are in different cells, edit G2:G13 and H2:H13 in the formula bar so both ranges cover the same rows.

Calculate the Combined Q1 and Q3 Average in WPS Spreadsheet
Use SUMIF and SUMPRODUCT in WPS Spreadsheet to average numeric values for both quarters and display a message when there are no matching values.
- 1. Open the spreadsheet: Open your workbook in WPS Spreadsheet and locate the quarter labels and corresponding values.
- 2. Select an empty cell: Click the cell where you want to display the average.
- 3. Enter the formula: Enter =IFERROR((SUMIF(G2:G13,"Q1",H2:H13)+SUMIF(G2:G13,"Q3",H2:H13))/SUMPRODUCT(((G2:G13="Q1")+(G2:G13="Q3"))*ISNUMBER(H2:H13)),"No matching numeric values") and press Enter.
- 4. Adjust the ranges: Edit the formula in the formula bar if your quarter labels and values are in different rows or columns.

Frequently Asked Questions
Why does the average formula return #DIV/0!?
The error appears when there are no numeric values to average for the matching criteria. Wrap the formula in IFERROR to show a message instead.
Can I add the Q1 and Q3 AVERAGEIFS results?
No. Adding the two averages does not produce their combined average. It can also return #DIV/0! if either quarter has no numeric values. Average the matching rows together instead.
Are blank cells included in the average?
No. The Excel formula checks H2:H13 for nonblank cells, and AVERAGE uses the matching numeric values. A numeric zero is included because it is a value, not a blank.
How do I change the message shown when there are no matches?
Edit the text in the final argument of IFERROR, such as "No matching numeric values", in the formula bar. Keep the replacement message inside quotation marks.




