Calculate an Average for Only No and N/A Responses in Excel
Question details
Calculate an average for a value range while including only records whose response status is No or N/A.
- Product
- Microsoft Excel spreadsheet formulas
- Device & OS
- not provided
- Scenario
- A probability and severity calculation must exclude rows marked Yes or Waiver.
- Observed behavior
- The desired result is an average based only on rows where the status column contains No or N/A.
Confirm that the status values in column B are consistently entered as No or N/A, without extra spaces. Make sure the corresponding probability or severity cells contain numeric values.
Use AVERAGE and FILTER to include only No and N/A rows
Use this formula when you have Excel or WPS Spreadsheet support for dynamic array functions such as FILTER.
FILTER returns only the numeric values from rows whose status is No or N/A. AVERAGE then calculates the result from that filtered list, excluding Yes and Waiver rows.
Click the cell where you want the conditional average to appear, such as the cell beside your probability or severity summary.
For values in F2:F100 and statuses in B2:B100, enter: =IFERROR(AVERAGE(FILTER(F2:F100,(B2:B100="No")+(B2:B100="N/A"))),"")
Replace F2:F100 with the probability or severity range you want to average, while keeping B2:B100 as the matching response-status range.
Press Enter. The formula averages only rows labeled No or N/A; IFERROR returns a blank result when no qualifying numeric rows exist.
Use SUMIFS and COUNTIFS when FILTER is unavailable
Use this alternative in spreadsheet versions that do not support the FILTER function.
Calculate Conditional No and N/A Averages in WPS Spreadsheet
WPS Spreadsheet lets you build a conditional average that includes only No and N/A responses. Use FILTER with AVERAGE to exclude Yes and Waiver rows from probability, severity, or other assessment calculations.
- 1. Open the workbook in WPS Spreadsheet: Open your Excel workbook in WPS Office and select the worksheet containing the response-status and value columns.
- 2. Choose the output cell: Click the blank cell where the conditional average should be displayed.
- 3. Enter the FILTER average formula: Type =IFERROR(AVERAGE(FILTER(F2:F100,(B2:B100="No")+(B2:B100="N/A"))),"") and update F2:F100 to your value column.
- 4. Confirm the calculation: Press Enter to calculate the average from No and N/A rows only, excluding Yes and Waiver entries.
Frequently Asked Questions
How do I exclude Yes and Waiver responses from an average in Excel?
Use a FILTER condition that includes only No and N/A, such as =AVERAGE(FILTER(F2:F100,(B2:B100="No")+(B2:B100="N/A"))). Rows marked Yes or Waiver are not returned by FILTER.
Why does my conditional average show an error when no rows match?
Wrap the formula in IFERROR, for example =IFERROR(AVERAGE(FILTER(F2:F100,(B2:B100="No")+(B2:B100="N/A"))),""). This returns a blank instead of an error when there are no qualifying rows.
Can I use the same formula for probability and severity columns?
Yes. Keep the status range in column B the same and replace the value range with the relevant probability or severity range. The ranges must cover the same rows.
Does the formula treat N/A as text?
Yes. In the condition B2:B100="N/A", N/A is treated as a text response value. It must match the text entered in the status cells exactly.
