Overview
The IFERROR function returns a user-defined result when a formula evaluates to an error, and returns the expected result when no error is detected. IFERROR is a way to trap and handle errors without having to use complicated nested IF statements.
Syntax
=IFERROR(value, value_if_error)
Arguments
value – Any value, expression or reference to check for an error.
value_if_error – The user-defined result when the formula evaluates to an error.
Version
Excel 2007 and onward
Purpose
The IFERROR function is used to trap and handle errors when an error is detected within a value, expression or reference. When the value returns a normal or expected result, the IFERROR function returns the normal or expected result. It is only when the value results in an error that the IFERROR function changes the result.
An alternative to the IFERROR function is the ISERROR function, which checks to see if there is an error in a target value, expression or reference. As there is one correct way to manage and handle errors, it’s always great to have options.
Examples
The IFERROR function can handle the following types of errors:
- #REF!
- #DIV/0!
- #N/A
- #VALUE!
- #NUM!
- #NAME!
- #NULL!
Using IFERROR to handle your errors can be extremely beneficial for maintaining data integrity. For larger workbooks with many dependent cells, a single error can cascade quickly into a large problem.
Let’s look at the following example, where we are trying to determine the hourly rate of a contractor, based on what they were paid and the number of hours they worked:
=(C4/C6) // Results in a #DIV/0! error
Since we are getting a #DIV/0! error, it appears the hours the contractor worked were not entered properly. Instead of this showing up an error, let’s trap and handle this error using the IFERROR function.
=IFERROR(C4/C6, “Check Inputs”) // Handles the error
Similar to the IF function, the value_if_error argument needs to be bounded by quotation marks if the result is a string value. If it is numerical, it does not need to be bounded by quotation marks:
As mentioned above, there is no one-correct-way to trap and handle errors. Depending on your application, you may want to only determine if a formula or expression is resulting in an error, as opposed to automatically handling and correcting that error.
As such, below is a list of other error functions in Excel:
- IFERROR function – traps errors and provides a user-defined result
- ISERROR function – returns TRUE for any error
- ISERR function – returns TRUE for any error except the #N/A error
- IFNA function – traps #N/A errors and provides a user-defined result
- ISNA function – returns TRUE for only #N/A errors
- ERROR.TYPE function – returns the numeric code for any given error
Notes
- If value_if_error is left blank, it will default to FALSE
Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has been consulting on and teaching best practices in Microsoft Excel to over 400 companies across 30+ countries.
Want to learn more?
Join My Excel Academy Today!
This All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
30-Day Money-Back Guarantee
This all-access pass includes:
- Access to all My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
Don’t hesitate – join now!
