|Value||Required||This is typically a formula that you want to check to see if it evaluates into an error. Any value can be inserted into this input.|
|Value If Error||Required||The value you want returned if an error occurs in the first input|
What Is IFERROR?
This function was created by Microsoft in Excel 2007 and therefore does not exist in the 2003 version of Excel. Its purpose is to prevent a formula from outputting an error (#N/A, #DIV/0, etc...). This can be helpful in preventing formulas dependent on other formulas (such as a subtotal) to error out. The IFERROR function can also be used to make your spreadsheet more visually appealing by ridding your cells of those nasty-looking error values. Caution must be used though, because adding this function to a formula will make it much harder to notice possible errors that could arise from different values in your cells.
Back In The 'Ole Days
Prior to the creation of the IFERROR function, you may have had to use a formula that looked like this to rid your cells of #DIV/0 errors.
As you probably can imagine, if a gigantic formula required the handling of errors in various parts, adding extra IF functions was never a fun task. Luckily, most of us now can shorten our formulas enormously. By using an IFERROR function we can shorten our formula to state:
That's a length reduction of almost 40%! Using IFERROR is also much easier to read and comprehend. There are just too many important benefits not to be using this function!
Real World Uses
Now that you've gone through what IFERROR does, you need to learn how you can put it to use. Let's skip all the hypothetical stuff and go straight into practical ways you can start using this function TODAY! Feel free to download the following examples by clicking on the green download link:
More Descriptive Error Messages
Instead of showing those hideous default error messages, you can use IFERROR to output an error message that is a little more intuitive. This could be as simple as choosing to output "Not Found" or as complicated as outputting a set of directions to troubleshoot your data.
Cleanup Future Month's Data
I try to always setup my yearly Excel workbooks for all 12 months. This means carrying formulas out to the end of the year and forgetting about them. The problem I see a lot (this is definitely a personal pet peeve of mine) is when formulas error out with blank data, they spit out all sorts of ugliness onto your spreadsheet. If you take the time to process the picture above, you will notice that your eyes are first drawn to the #DIV/0! errors instead of the data. For me, this is unbearable! Luckily we can throw an IFERROR function around our formulas and act as if those errors never existed!
Guru Tip: Instead of manually typing out IFERRORs around each of my formulas, I have a personal macro that will automatically do it for me! Check out Wrapping an IFERROR Function Around Your Formulas from the Excel Code Vault
This is a must for any situation where you are trying to subtotal data that relies on dividing. When you have data that could hit zero and that data is the divisor (an example could be a specific product's volume for a given month), there may be instances where your formula tries to divide by zero.
In case you didn't know, it is mathematical impossible to divide by zero! Want proof? Check out this great post over at the Bacon Bits Blog to learn The Mathematical Reason You Can Never Divide By Zero
Anyways, formulas such as SUM and AVERAGE really don't like it when you try to feed them errors. So, in order to keep them happy you can add an IFERROR formula to prevent any errors from getting to them.
Perform Multiple Table Lookups
Have you ever had instances when you needed to perform a VLOOKUP function across multiple tabs or sets of data? With nested IFERROR functions you can create this functionality very easily! Since a VLOOKUP outputs an #N/A error when it cannot find an exact match, you can keep nesting IFERROR functions to look at however many datasets you need until a match is found. Here's an example to show you how you can write a single formula to perform HLOOKUPs across two sets of data (as shown above):
Why Do You IFERROR?
Now that I have gone through some scenarios that I use IFERROR with, I want to hear why you use the function. Drop me a note in the comments section below!