Iferror: Great Function for Keeping Your Datasets Clean

There are times when analytic functions performed on a perfectly regular dataset will yield mathematically impossible results. The most common of these are samples that require dividing by zero. Excel deals with these mathematical impossibilities by calling out the error with something like this #DIV/0!. This is a good way for Excel to highlight possible mistakes in the function, but is unsightly and annoying when you know that your formula is typed correctly. So, what can you do to keep your tables clean? Enter the iferror function.

The iferror function has exactly one purpose, to deliver an output other than the error message when any error occurs. The syntax is straightforward:

=iferror(function,output if function errors)

For example:

=iferror(C37/C6,0)

This is a simple division of the value in cell C37 by the value in C6. If the value of C6 data is 0, the output will be 0 rather than #DIV/0!

Another twist:

=iferror(C37/C6,”—")

This is the exact same function, only the output inn an error case will be – rather than 0 or #DIV/0!.

I use this function all the time to keep all of my analysis worksheets. The big point of note is that you are essentially turning off Excel’s error troubleshooting functionality, so it is important that you are very comfortable reading and writing and reviewing your formula. I also strongly recommend spot-checking all of the cells that are outputting whatever you placed as the error response, just to be sure that it makes sense.

Watch this short tutorial to see the function in action!