99.4% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
How To Disguise Error Messages In Excel 2007/2010
Thu 23rd June 2011
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.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Projects And Operations Administrator
Course was perfectly paced and presented. Really helpful and patient instructor. I'd used excel previously but this has improved my overall confidence. I now won't need to ask people how to do things as much!
Crowne Plaza London City
I think everyone should attend this course. It's very useful!!!
Deputy Manager, Joint Venture Assets &Projects
From Storyboard to Presentation Deck
The PowerPoint training was superb and met my needs.