SPREADSHEETS - 10.01.2022

How to handle formula errors in Excel

Formula errors

Whenever Excel is unable to perform a calculation or action correctly, it displays an error message in the cell itself. An error message always starts with a hash sign (#).

#NA: The formula can’t find what it’s supposed to look for.

#VALUE!: An argument has the wrong type.

#REF!: A formula refers to an invalid cell.

#DIV/0!: Appears when the formula tries to divide by a cell that is either blank or has the value zero.

#NUM!: The formula contains numeric values that aren’t valid.

#NAME?: Usually appears when the name of a function is misspelled.

#NULL!: Appears when you specify intersecting points that don’t actually exist.

In most cases, you respond to these errors by trying to resolve the underlying problem. But other times you might be expecting them and don’t want to resolve them directly. That’s where the IFERROR function comes in.

IFERROR

The IFERROR function allows you to replace the displayed error value with a custom message or another value. Tip. If you create a formula and then realise it needs to handle errors, you can insert the IFERROR function at the start of your formula.

The IFERROR has two arguments, or parts. The first argument is the calculation to perform, and the second is what to return if the calculation returns an error. If the calculation doesn’t return an error, then its result is displayed. If the calculation does return an error, the second argument is displayed.

The syntax of the IFERROR function is:

=IFERROR(Value; Value_if_error)

Value is the argument that you want to check for an error and Value_if_error is what you want to happen if an error is found. Tip. If you want the Value_if_error to be text, it must be enclosed in double quotation marks.

Example. Let’s say that you have a formula such as =B2/C2. If cell C2 contains a zero, then the formula will return the #DIV/0! error. The IFERROR function can wrap around the above formula to handle a zero value in C2, e.g. =IFERROR(B2/C2,0) which will return a zero rather than the #DIV/0! Error. A =IFERROR(B2/C2,””) would return a blank cell. Tip. Excel also has an ISNUMBER function. It returns the values TRUE or FALSE depending on whether an error is found. However, most users embed it in an IF function. IFERROR eliminates the need for nested statements.

IFERROR limitations

While the IFERROR handles nearly all Excel’s formula errors, you should be aware of its limitations.

1. The IFERROR can, to some extent, mask or hide real errors. For example, if the formula returns a #VALUE error, the above IFERROR formula will still display 0 or a blank cell.
2. In the subscription version of Excel, IFERROR doesn’t handle the #SPILL error, which is related to dynamic arrays.

For an example spreadsheet with the IFERROR function, visit https://www.tips-and-advice.co.uk , Download Zone, year 14, issue 04