To Error is Human…

Mistakes happen in Excel.  There is no sure cure for this.  “To Error” is indeed “Human”.  Excel is designed to let us mere mortals know when you make a mistake by giving us an Error Message.  This can be helpful, but it can also be rather annoying!

What, for instance, do all of these would-be-helpful Error Messages mean?!?  And What can you do about them?  As we did a couple of years ago, let’s look at a list of Error Messages along with some brief explanations:

• #DIV/0! - You divided by 0 or by an empty cell (We all learned in algebra that you can’t do that…)
• #N/A - A return value of the function is not available (Yeah, you probably messed up the formula)
• #NAME? - Well, you might have used an Undefined range or cell name (easy to correct…).
• #NULL! - You specified an intersection of two areas that do not intersect (Weird, eh?).
• #NUM! - There is a problem with a number in a formula (i.e. Is it really a number?).
• #REF! - An invalid cell reference is mentioned in a function (reasonably self-explanatory…).
• #VALUE! - The wrong type of argument or operand is used in the formula (Okay, fine!)

So, the foregoing covers “What do they mean”.  Now let’s look at “What can you do” by examining a few useful Handlers that can be applied in your worksheet:

1.   ISERR - Any error value except #N/A
2.   ISERROR - Any error value, including #N/A, #VALUE!, #REF!, NAME?, etc
3.   IFERROR – Introduced in Excel 2007, this combines the IF and ISERROR functions

Let’s use the IFERROR in an example. Suppose you have two columns of data which you are simply dividing in a third column. If you Divide by 0 or Divide by a Blank Cell, you will obviously get an Error Message.

 If, however, you want the message to be Clear and Concise to the potential user, you can use IFERROR to define a Custom Response such as “Mistake in Calculation” or “Problem with 2^nd Value”, or something more apropos that you can invent for your own special circumstances. In the example shown, the formula would be:

=IFERROR (Column_1 / Column_2, “Error in Calculation”)

 

By creating Custom error responses, rather than accepting sometimes cryptic stock Error Messages, you can Humanize your worksheets.  To Error, after all, is Human…