Spreadsheet errors appear as unprofessional and can put the integrity of your whole report into question. However, there are some circumstances where spreadsheet errors are unavoidable. By using the IFERROR statement, you can manage how errors appear on a spreadsheet.
Quick Tip: IFERROR
IFERROR is a useful spreadsheet function added in Excel 2007. It helps eliminate extraneous spreadsheet errors in a single statement. The IFERROR function will test if a formula (first parameter) will return any common errors, including: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. If an error is returned, the function will return a user-specified value (2nd parameter) instead of the error. If no error is returned, the value from the formula will be presented.
Basic Application Examples
Division by Zero
Will return “#DIV/0!”, a division by zero.
Will return “0”. Will not appear as an error and will not look unusual.
Can be used in VLOOKUP formulas where a value may not exist in a table.
The VLOOKUP formula within the IFERROR statement searches an inventory list for the quantity of bananas. Given that there is no banana quantity, and it doesn’t exist in the table, the error “#N/A!” would normally be returned. However, now that it is in an IFERROR statement, the value “No Inventory” will be returned.
Can be used to help users properly fill out forms, or even validate data.
The above example returns a mathematical error. The user should have entered the number 4 instead of spelling “four”.
This formula will provide the user with a helpful tip, informing them of the error and providing a helpful tip.
Can Be Used In Place Of