Excel Hack - Error Checking

A wise person once said "To err is human".  Well Excel sometimes creates errors as well.  Luckily there's a built-in way to check for errors and deal with them.  A common error in computing is dividing by zero.  Here's an example:

Cell C2 shows the formula that's been used.  The "/" part of the formula is how you do divide in Excel and so "=A2/B2" means "take the value in cell A2 and divide it by the value in cell B2".  

You can see how in cell B5 there is the number 0.  In maths (or math for our American friends), a divide by zero is either called infinity or "undefined".  Either way, it can't be computed.  Hence Excel puts an error in a cell where you do a divide by zero, in this case "#DIV/0!".

You can test for a divide by zero using the "ISERROR" formula.  Here's an example:

So the formula "=ISERROR(C2)" simply means "look at the value in cell C2, if it's an error print TRUE, if it's not an error print FALSE".

You can then use an IF formula (see here for an explanation) to test for the error like this:

So the formula above says "look at the value in cell D2.  If it's TRUE print a blank cell, otherwise print the result of the division that was in cell C2.

In the above image I've applied the formula to row 5 where we have the divide by zero and it's come out as blank.  What to set the cell to where there is an error is a design choice.  For example:

• Where there is a divide by zero error you could set the result cell to 0. However if you're also computing something like an average of the column overall you will get a skewed result.  (The average of 3, 3, 9 and 0 is 5.25, the average of 3, 3, 9 and blank is 7).
• You could set the cell to something like "ERROR - FIX ME" then you can search and fix these errors.

What I've shown in the image above is something I often do in Excel to make things more maintainable.  Specifically there is one formula per column (so divide in column C, error check in column D, IF statement in column E).  

You can also chain together more than one formula in a cell like this:

In the above formula in column F we have a error check inside an IF statement.  The way to interpret the formula is:

• So "ISERROR(A2/B2)" to first check if the result is an error.
• If it is an error, make the cell blank.
• If it is not an error, compute A2 / B2 and put it in the cell.

It's up to you how you choose to work.  You can use multiple columns to build up the logic one column at a time or do one big multiple formula entry in a single cell.  I choose the multiple columns option as it makes it easier weeks or months later when you come back to a spreadsheet and try and work out what you actually did!

There's also an "IFERROR" formula that does what I've done above in a single formula.  Additionally there's a bunch of other errors you can check for.  Have a play, see what you can come up with and comment below!