Fix an Excel AVERAGE Formula Returning FALSE
Question details
Calculate the average of values in column M for rows where column H contains the value "cottage", while excluding errors.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An array formula checks column H for matching rows and averages the corresponding values in column M.
- Observed behavior
- The formula returns FALSE instead of a number, even though matching rows exist and the formula was entered with Ctrl+Shift+Enter.
Check that the source sheet is named data and that ranges H5:H185 and M5:M185 cover the intended rows. Confirm that matching cells in column H equal "cottage" and that at least one corresponding cell in column M contains a number.
Use an array formula that excludes errors
Use this formula to average matching values while ignoring errors in column M.
The inner IF excludes errors in column M before AVERAGE calculates the result. The outer IFERROR returns a blank if the calculation still produces an error.
Select the result cell and enter =IFERROR(AVERAGE(IF(data!H5:H185="cottage",IF(ISERROR(data!M5:M185)=FALSE,data!M5:M185))),"").
Press Ctrl+Shift+Enter in legacy Excel versions. In Microsoft 365 and other versions with dynamic arrays, press Enter.
If the result is blank, inspect the matching rows on the data sheet for numeric values in column M and errors in column H. The outer IFERROR also hides errors such as having no numeric values to average.

Clean column M and use AVERAGEIF
Use a helper column if you prefer a simpler formula without array entry.
Average matching values in WPS Spreadsheets
Use a helper column and AVERAGEIF in WPS Spreadsheets to average matching rows while excluding errors from the source values.
- 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
- 2. Clean the source values: On the data sheet, enter =IFERROR(M5,"") in an unused cell such as N5, then fill the formula down through N185.
- 3. Enter the average formula: Select the result cell and enter =IFERROR(AVERAGEIF(data!H5:H185,"cottage",data!N5:N185),"").
- 4. Save the workbook: Press Enter to calculate the result, then choose File > Save As to save the workbook in your preferred format.

Frequently Asked Questions
Why might an Excel formula return FALSE instead of an average?
An IF expression can return FALSE when its condition is not met and no value for the false case is specified. Check the formula's conditions and use an AVERAGE formula that evaluates the matching rows.
Does the criterion "cottage" match a cell containing "cottage rental"?
No. Use "*cottage*" as the criterion in AVERAGEIF if you want to match cells containing the word within longer text.
Why does the corrected formula return a blank?
The outer IFERROR replaces calculation errors with a blank. Check whether any matching rows have numeric values in column M and whether column H contains errors; remove the outer IFERROR temporarily to see the underlying error.
Do I need Ctrl+Shift+Enter for the array formula?
Use Ctrl+Shift+Enter in legacy Excel versions. In Microsoft 365 and other versions that support dynamic arrays, enter the formula with Enter.




