What it does: The IFERROR() Function is a function designed to trap errors when they occur. It is enacted when the argument/formula inside of the parentheses generates any of the following errors:
- #N/A
- #VALUE!
- #REF!
- #DIV/0!
- #NUM!
- #NAME?
- #NULL!
If there is no error with the original formula, the original formula is run with no additional parameters.
Plain English: This function is awesome. While sometimes an ERROR in a function could result from misusing syntax or not building arguments correctly, often ERROR’s can be a good thing! IFERROR allows us to tell Excel what to do if and when there IS an error. It acts like a safety net for your formulas.
Example: Let’s say a client has asked you to replace all instances of “Example.net” to “Example.com” on her website. She changed domains but does not have the time to replace links across all 1,000 blog posts on her site. You have a tool that allows you to download the meta data from every blog post to your computer. Once the domains are replaced, you have a tool that allows you to upload a .csv of the new blog posts to the website and the changes are made instantly.
- The first part of the project is to search for the domain “Example.net” in the blog posts. The formula is =search(“Example.net”,CELL)
- Can you think of why you may get an error?…
What if there is no mention of “Example.net” in 125 of the blog posts? You’ll see an error in 125 out of the 1,000 cells. (a #VALUE error)
- Our new formula is =IFERROR(search(“Example.net”,CELL),”No Domain”)
The new formula above will run the exact same search on each cell, but if there is an error, it will spit out the text “No Domain”. From there we can trap and deal with those cells separately.
Arguments: IFERROR(value,value if error)
- Value – The value or formula you want to use first
- Value if error – The value or formula you want to enact IF there’s an ERROR with the first one.
Pingback: Top 5 Things to Know in Excel | Every Day I'm Pivoting