To use IFERROR with AVERAGE in Excel, wrap the average calculation in IFERROR: with the values to average in B2:B10, enter =IFERROR(AVERAGE(B2:B10),"No numeric values"). Excel returns the average when the calculation succeeds and the replacement text when it produces an error.
How IFERROR and AVERAGE work together
AVERAGE calculates the arithmetic mean: it adds the numeric values in its arguments and divides their sum by the number of numeric values. IFERROR checks the result of that calculation. If AVERAGE returns a number, IFERROR passes that number through; if it returns an error, IFERROR supplies the alternative value you specify.
This is useful because AVERAGE can return an error when there is nothing numeric to average, and it also returns an error if a referenced cell contains an error. Putting IFERROR around the calculation can keep a report or dashboard readable while the underlying data is incomplete or being corrected. It does not repair the source data; it changes what the formula displays when something goes wrong.
The arguments are in this order: the calculation to check, followed by the value to return if that calculation produces an error. The fallback can be text, a number, a blank-looking result, or another calculation. Choose it to suit the meaning of the worksheet rather than simply hiding every problem.
Basic formula and setup
Suppose the measurements are in B2:B10. Select the cell where you want the result, such as C2, and enter this formula:
With measurements in B2:B10 and the result in C2, use =IFERROR(AVERAGE(B2:B10),"No numeric values").
When B2:B10 contains at least one usable number and no error values, C2 displays the average. If the range is empty or contains no numeric values, AVERAGE returns #DIV/0!, and C2 displays “No numeric values” instead. The text in quotation marks is a message chosen by you; it is not a special Excel phrase.
You can replace the message with a numeric fallback. With values in B2:B10 and the result in C2, enter =IFERROR(AVERAGE(B2:B10),0) to display zero if the average calculation errors. This is convenient for calculations that require a number, but it can be misleading: readers may interpret that zero as a genuine average. A label such as “No data” often communicates the situation more clearly.
What AVERAGE includes and what causes an error
For a range reference such as B2:B10, AVERAGE includes numeric cells, including cells whose value is zero. It ignores empty cells and text in cells, including a formula result that is an empty text string. Therefore, blanks and text labels do not ordinarily make an otherwise valid average fail. If the range has no numeric values, however, Excel has no count by which to divide and returns #DIV/0!.
An error value in the referenced range is different from a blank or a text label. For example, a cell containing #N/A can cause AVERAGE to return an error rather than calculate from the other numbers. IFERROR then catches that error too. This distinction matters: a “No numeric values” fallback might be accurate for an empty range but inaccurate if the real problem is a broken lookup or a bad reference.
AVERAGE also treats some inputs differently when they are supplied directly as arguments rather than as cells in a range. For routine worksheet calculations, the usual pattern is to reference a range and remember that text and blanks in the range are ignored, while error values can interrupt the calculation.
Worked examples with results
For a straightforward average, suppose B2:B5 contains 10, 15, 20, and 25. With those values in B2:B5 and the result in C2, enter =IFERROR(AVERAGE(B2:B5),"Check data"). The sum is 70 and there are four numbers, so C2 displays 17.5. IFERROR does not change a successful calculation; it simply leaves the result as calculated by AVERAGE.
Now suppose B2:B5 contains 10, 15, #N/A, and 25. With those entries in B2:B5 and the result in C2, use =IFERROR(AVERAGE(B2:B5),"Check data"). AVERAGE encounters the error, so C2 displays “Check data” rather than the average. That message signals a problem but does not identify which cell caused it. If the error needs investigation, locate and correct the source cell instead of treating the fallback as a valid average.
For a range with no numeric entries, suppose B2:B5 is blank or contains only text labels. With that range in B2:B5 and the result in C2, =IFERROR(AVERAGE(B2:B5),"No numeric values") displays the message because AVERAGE has no numbers to calculate. If B2:B5 instead contains 10, 0, and 20, the average is 10: zero counts as a numeric value, while a blank cell does not.
Choosing a useful fallback
The fallback should tell users what to do with the result. A descriptive text value is often best for a presentation cell or report. A numeric fallback can be helpful when downstream formulas must receive a number, but it is important to distinguish a substitute from a measured result. A zero may change later totals or averages as if a real zero had been recorded.
- Use a clear message when the average is unavailable, such as “No data” or “Check source values.”
- Use an empty-looking result when a blank display is appropriate, but document what it means if users might mistake it for an empty cell.
- Use a number only deliberately when a later calculation needs one and the chosen fallback makes sense for that calculation.
- Use a specific message when you know the expected failure, rather than implying that every possible error means the same thing.
To show a blank-looking result, with values in B2:B10 and the output in C2, enter =IFERROR(AVERAGE(B2:B10),""). The two quotation marks represent an empty text string, not a truly empty cell. Other formulas can still refer to C2, and Excel may treat that text result differently from a cell that contains nothing. If users need to tell the difference, a visible message is safer.
When the average should use a condition
IFERROR does not set criteria for which rows to average. If you need an average only for rows meeting one condition, use AVERAGEIF and, if appropriate, wrap that calculation in IFERROR. For example, with regions in A2:A10, sales in B2:B10, and the result in C2, use =IFERROR(AVERAGEIF(A2:A10,"East",B2:B10),"No matching numeric values"). AVERAGEIF averages the sales values for rows where the corresponding region is East; the fallback appears if that calculation returns an error, such as when there are no qualifying numeric values.
For multiple conditions, Excel provides AVERAGEIFS. With regions in A2:A10, sales in B2:B10, and status values in C2:C10, a formula can average sales for East rows marked Closed. Put the result in D2 and enter =IFERROR(AVERAGEIFS(B2:B10,A2:A10,"East",C2:C10,"Closed"),"No matching values"). IFERROR still handles the result; AVERAGEIFS determines which records qualify. Check that the criteria ranges and average range cover corresponding rows.
IFERROR compared with other approaches
IFERROR catches any error returned by the expression it evaluates. That simplicity is useful when the same fallback is appropriate for all errors, but it can conceal different causes, including a missing value, a misspelled reference, or a source-data error. Use a message such as “Check data” when the exact cause is uncertain, and investigate the range when accuracy matters.
IFNA is narrower: it replaces #N/A errors rather than every error type. If the only expected issue is a missing lookup value and other errors should remain visible, IFNA may preserve more useful warnings. It will not, however, replace the #DIV/0! that AVERAGE returns for a range with no numeric entries.
If you want to average numeric values while ignoring error cells in the referenced range, consider AGGREGATE rather than simply wrapping AVERAGE in IFERROR. With values in B2:B10 and the result in C2, enter =IFERROR(AGGREGATE(1,6,B2:B10),"No numeric values"). In this use, function number 1 requests an average and option 6 tells AGGREGATE to ignore error values. IFERROR still provides a fallback if there are no usable numbers. This changes the calculation policy, so use it only when excluding error cells is appropriate for the analysis.
Common problems and checks
- The result is a message when you expected a number: inspect the referenced cells for errors and confirm that at least one numeric value exists. IFERROR may be hiding the reason AVERAGE failed.
- The average seems too low or too high: check for zeros, which AVERAGE includes, and check that the selected range contains exactly the intended records.
- The formula shows an error rather than the fallback: confirm that the error is produced inside the expression covered by IFERROR and that the formula is entered correctly.
- The result is blank but downstream calculations behave unexpectedly: an empty text string from
""is not the same thing as a genuinely empty cell. - The formula is rejected as you type it: Excel’s argument separator can depend on regional settings. Many installations use commas as shown here; some use semicolons. Follow the separator expected by your Excel installation.
On Microsoft 365 for Windows, type the formula in the cell or formula bar and press Enter. Excel for Mac uses the same functions and formula structure; keyboard shortcuts and regional separators may differ by system settings. Excel for the web also supports these functions. The main formula does not require a special add-in or a newer function such as XLOOKUP.
Frequently Asked Questions
Can I use IFERROR with AVERAGE in Excel?
Yes. Put AVERAGE as the first argument of IFERROR and the fallback as the second, such as =IFERROR(AVERAGE(B2:B10),"No data"). The average appears when successful; the fallback appears if the calculation returns an error.
Does AVERAGE ignore blank cells and zeros?
AVERAGE ignores blank cells and text in a referenced range, but it includes numeric zeros. A zero can therefore lower the result, while a blank does not add to either the sum or the count.
Will IFERROR ignore errors inside my data range?
No. IFERROR replaces the error result from the whole AVERAGE calculation; it does not make AVERAGE skip the error cell. To ignore errors while calculating, use a suitable method such as AGGREGATE with its ignore-errors option, and confirm that excluding those records is appropriate.
How do I show a blank instead of an error?
With the data in B2:B10 and the output in C2, use =IFERROR(AVERAGE(B2:B10),""). This displays an empty text string when the calculation errors, rather than leaving C2 truly empty.
Should I use IFERROR or IFNA?
Use IFERROR when you want to replace any error from the calculation. Use IFNA when only #N/A should be replaced and other errors should remain visible. For an empty average range, AVERAGE returns #DIV/0!, so IFNA alone will not catch that case.
Related
- Excel Data Model: How to Append Tables with Power Query
- Excel Data Run Analysis: A Practical Runs Test
- Excel Data Model in the Web: What Works and What Doesn’t
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Month and YearIf the date is in A2, enter =TEXT(A2,"mmmm yyyy") in B2 to display its month and year, such…
- Excel LEFT Function: How to Extract Text from the StartTo return characters from the beginning of text in Excel, enter =LEFT(A2,3) in a cell, replacing A2 with…
- Excel Formula for Hours Between Two TimesTo calculate hours between two times in Excel, subtract the start time from the end time and multiply…
- Conditional Formatting If Statements in Excel: A GuideIn Excel, an “if statement” for conditional formatting is usually a formula-based rule that returns TRUE or FALSE;…
- Excel Formula for Balance Remaining: Simple ExamplesThe basic Excel formula for balance remaining is =B2-C2: subtract the amount paid in C2 from the original…
- Excel Attendance Sheet With Time: Setup and FormulasTo make an Excel attendance sheet with time, create columns for the date, employee, scheduled start, clock-in, clock-out,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.