I’m not sure why, but less people use the IFERROR function than I would expect. If you’re someone that likes to see neatly organized and clean spreadsheets (like me) then this section is for you. I cringe when I open a sheet that is riddled in formula errors: #N/A, #VALUE!, #DIV/0! are all signs of a broken spreadsheet (also the bane to my existence), and just looks plain sloppy.
How it works
=IFERROR(Value,Value if error)
IFERROR saves the day. This function can take the result of any formula or function and turn it into anything you want. You can get rid of all the errors in your sheet by telling the function to return a blank cell, some text, or another function (Yeah, more nesting!). And its one of the simplest functions to use:
Value – This is whatever you are evaluating. In this portion of the function you must enter either a formula or reference a cell with an error. IFERROR will evaluate whether the value returned is an error or not.
Value If Error – Pretty self-explanatory, this is where you tell excel what value to return if it evaluates the result of the previous argument as an error. You can make the cell blank (enter double quotes, ie. “”), text (enter text inside of quotes, ie. “This is some text”), a number, a cell reference, a function, anything!
IFERROR is pretty flexible because of all the options it gives you to address errors in a sheet. Lets look at a practical application of the IFERROR.
Lets Get Practical
Lets say you are using a VLOOKUP to cross reference one list with another. Based on the last few chapters, we already know that the VLOOKUP will return a #N/A error if it doesn’t find the lookup value in the table (for more information, read the chapter on VLOOKUPS).
In this example, the products that returned an error are obviously not found in our product database (on the left). No matter, because the boss wants a total of the units in stock for the list she sent us so we better get to it.
There is one problem though; when we use a SUM function to total up the units in stock, we get another error:
The SUM can’t give us a total because of all the #N/As. Lets use IFERROR!
I want the IFERROR to return a zero because they don’t exist in the original table. If they don’t exist, they probably have zero units in stock.
Lets try this again:
All of our errors are gone! Now you can impress your boss with the exact number she needs, and a fancy table to go along with it.