IFERROR in Excel: when to replace an error and when to show it

IFERROR checks the result of a formula. If it is an error, it returns a value I choose. If it is not, it passes the result through unchanged. That keeps a report from turning into a wall of #DIV/0! and #N/A.

The convenience has a price. An error in Excel is often the only sign that data is incomplete or that a formula points to the wrong range. So before I wrap a formula in IFERROR, I decide whether I want to hide the error or just describe it better.

IFERROR syntax

=IFERROR(value, value_if_error)

• value: the formula or reference I check.
• value_if_error: what should appear instead of an error. It can be text, a number, an empty string "" or another formula.

According to Microsoft the function reacts to #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!. An empty cell in either argument is treated as an empty string. Localised Excel versions translate the function name and may use a semicolon as the argument separator.

Example: price per unit

I have a synthetic order table in A1:C6 and calculate the price per unit in column D with =B2/C2:

In the pen row I divide by zero. In the backpack row someone typed the quantity as a word, so dividing by text returns #VALUE!. Instead of raw errors I want readable information:

=IFERROR(B2/C2, "check data")

I put this formula in a new column E and keep column D with the raw quotient, because the calculations below need it. For the first three rows the formula returns the price, and for the last two the text “check data”. The idea comes from an older Polish version of this entry, which showed a similar message instead of an error. A message like that works well because it does not pretend to be a number and tells you where to look.

Which value to use instead of an error

The second argument matters more than the function itself. I choose it by what someone will do with the result.

• A text message, such as “check data” or “not in price list”, when a person reads the result and should know that something needs attention.
• An empty string "", when a missing result is normal and the cell should simply stay empty. Empty text is not included in AVERAGE or COUNT.
• Zero only when zero is the real answer. The pen’s price per unit is not zero; we simply do not know it.

The trap: an average with zeros

I want the average price per unit from column D, the raw quotients including the errors. A plain average over a range with errors returns an error itself, so this formula is tempting:

=AVERAGE(IFERROR(D2:D6, 0))

The result is 25.28, because two zeros join 24.90, 12.50 and 89.00, and the total of 126.40 is divided by five. The real average of the three valid prices is 42.13. Replacing errors with zero pulled the result down by about 40%.

There are two correct ways. Empty text, which AVERAGE does not count:

=AVERAGE(IFERROR(D2:D6, ""))

Or AGGREGATE, where function 1 is the average and option 6 ignores error values:

=AGGREGATE(1, 6, D2:D6)

Both return 42.13. In older Excel versions the first one has to be confirmed with Ctrl+Shift+Enter, because it works on an array. Next to a result like this I always state how many rows I skipped. Here it is two out of five.

When I do not use IFERROR

For lookups I prefer IFNA. It reacts only to #N/A, which means no match. If I get the column number wrong in VLOOKUP and receive #REF!, that error stays visible. IFERROR would turn it into “not in price list” and nobody would notice the formula is broken.

In XLOOKUP I do not need an extra function at all, because the fourth argument sets the value for a missing match:

=XLOOKUP(F2, A2:A6, D2:D6, "product not found")

I also do not wrap formulas that should never fail. #NAME? means a typo in a function name, and #REF! a deleted range. I want to see those errors immediately rather than discover them a month later in a report.

How to count the hidden problems

If I do replace errors, I count them next to the result. ISERROR returns TRUE for every cell with an error, and SUMPRODUCT adds those values up after turning them into numbers:

=SUMPRODUCT(--ISERROR(D2:D6))

The result is 2. A number like that in the report header tells the reader more than two tidy dashes in the table.

A short exercise

Copy the order table into a sheet. Calculate the price per unit in column D with a plain division, and next to it in column E the IFERROR version with a message. Then take the average of column D in three ways: with zeros, with empty text and with AGGREGATE. Compare the results. Finally, change “two” to the number 2 and check how the average and the error count changed.