Formula Errors

Average Q1 and Q3 Values in Excel Without a #DIV/0! Error

Huda QurayshiHuda Qurayshi Nov 18, 2026 869 views

Question details

Calculate a combined average of Q1 and Q3 values while excluding blanks and avoiding a division-by-zero error.

Average Q1 and Q3 Values in Excel Without a #DIV/0! 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.
Before you start

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.

Solution 1Recommended

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.

1
Select a result cell

Click an empty cell where you want the combined average to appear.

2
Enter the formula

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.

3
Check the ranges

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.

Use IFERROR with an Excel array formula
Check unexpected errors: IFERROR also replaces errors other than #DIV/0!. If the message appears unexpectedly, check the source cells for errors and confirm that the matching values are numeric.
Calculate in WPS Spreadsheet

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. 1. Open the spreadsheet: Open your workbook in WPS Spreadsheet and locate the quarter labels and corresponding values.
  2. 2. Select an empty cell: Click the cell where you want to display the average.
  3. 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. 4. Adjust the ranges: Edit the formula in the formula bar if your quarter labels and values are in different rows or columns.
Combine Q1 and Q3 values into one weighted averageIgnore blank and nonnumeric value cells in the countOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.