You will no doubt have seen one or more error message in Excel indicating a problem with data or a formula. These error messages are there for good reason, but there may be occasions when you want to disguise the errors or substitute a certain value in all cells containing an error messages. For example you might want to sum a range of cells even though some cells contain error messages. This article describes a very useful Excel 2007/2010 function which will do just that, the IFERROR function.

The Error Messages IRERROR will detect

The error messages IFERROR will detect are #NA, #VALUE, #REF, #DIV/0, #NUM, #NAME and #NULL. Before describing how to use IFERROR, we'll summarise how to interpret these messages.

#NA indicates a value is not available to a function or formula, for example as a result of a LOOKUP or MATCH which does not find a value.

#VALUE occurs if you have a reference in a formula containing text where a number was expected, or you enter a range of cells into a formula where a single value was expected.

#REF occurs when a cell reference is not valid, for example because cells in a formula have been deleted or overwritten.

#DIV/0 occurs when a formula results in a cell value being divided by zero or by a cell reference which is empty.

#NUM occurs when a number in a formula is invalid, for example caused by typing a £ symbol in a calculation rather than just the number, or where a number in a formula is too large or too small.

#NAME occurs when text in a formula is not recognized, for example using an incorrect range name, or mistyping a function name, or omitting quote marks round text.

#NULL occurs in cell ranges are entered incorrectly, for example with the range colon missing or if ranges are not separated by commas.

Using the IFERROR Function in Excel 2007/2010

This function looks something like =IFERROR(VALUE,VALUE IF ERROR) where VALUE is the cell being tested, and VALUE IF ERROR is the value to be substituted if one of the above errors is detected. We'll describe below how to use IFERROR to check a specific cell and substitute a zero if the cell contains an error. So if you want to check a column of cells, apply IFERROR to the first cell and then auto fill the formula down all the cells. Let's look at an example.

Suppose you have a column of data in cells D4 to D10 containing a mixture of numbers and error codes. You then type =SUM(D4:D10) into cell D11. However Excel shows an error message in cell D11, because some cells do not contain numbers. So your SUM calculation will not work. We will now use the IFERROR function to substitute the number zero for each error message and then we can use the SUM function.

We create a second column of values in E4 to E10 containing either the original values or a zero for any cells containing an error code. So in cell E4 we type =IFERROR(D4,0) and press the enter key. If cell D4 contained a number this should now show in Cell E4. If D4 contained an error message then E4 should show the value 0. We then use the fill handle to fill the function down all the remaining cells. This results in a second column of data with all cells containing either the original number or a zero. Now we can SUM this second column and all the values are added correctly.

Using substitutes for error messages

IFERROR allows us to substitute cells containing error messages with a number or text value or a blank cell, depending on how we want to disguise or hide the message. So substituting with a zero value or a blank cell allows Excel functions such as SUM which use cell ranges to still work normally. It's good practice to substitute zero rather than blank cell values for error messages to allow calculations to proceed. Just be aware that some functions such as AVERAGE will include cells with zero values in the calculation but not blank cells. As well as substituting numbers, you can substitute your own text values such as "Error in Calculation" to show a more meaningful display. Substituting blank cell values can be useful if you simply want to hide the error messages.

By the way in earlier Excel versions users could only use the ISERROR function to detect errors. The function returns a TRUE or a FALSE depending on whether an error message is detected or not. If you wanted to substitute a value for the error message you needed to combine ISERROR with an IF function.

Hopefully this article has given you a brief insight into how error messages can be trapped and substituted in Excel 2007/2010 but please remember the messages are there for good reason. However error trapping with IFERROR can be very useful in allowing calculations to proceed. Interested in finding out more about Excel? Why not consider attending a training course. The best ones give you lots of hands practice and can help you really boost your Excel skills.