Now that you’re getting into using formulas in Excel you’ve found they can be quite useful. And by now you’ve found that sometimes they return error values.
Sometimes, these can detect errors you need to fix. Other times, they just indicate that you don’t yet have all the data populated in your worksheet.
You wind up with a cell showing values such as #VALUE!, #DIV/0!, #N/A, etc. It just looks messed up and as if the whole sheet might be suspect.
You can add another function to your cell that will “wrap” your existing function with a small layer of error protection.
Meet IFERROR
The IFERROR function lets you simply show one value if your function runs normally, and alternate value (that you define) if your function returns one of Excel’s error values. Here’s an example.
=IFERROR(YourOriginalFormula, YourAlternateValue)
So if your original formula that was returning an error was, for example, “=A1/B1”. You would see a “#DIV/0!” error until you entered a value in cell B1 because the empty B1 cell is essentially a zero. To take care of the error showing, you could do any of the following as an example.
=IFERROR(A1/B1, "Waiting for input")
=IFERROR(A1/B1, "")
=IFERROR(A1/B1, 0)
And, in Excel 2013 IFERROR gets a new cousin: “IFNA”. IFNA works the same way as IFERROR except that is only reacts specifically to the #N/A error and ignores the rest.
There you go.
