Errors, Errors, Everywhere!

We’ve all Been There, Done That. You are creating an Excel masterpiece formula when you are troubled, nagged, and thoroughly bugged by some Error Messages.

The following is a handy Alphabetical Listing of Error Messages and what they mean:


• #DIV/0! - You divided by 0 or by an empty cell (Everyone knows you can’t do that…)
• #N/A - A return value of the function is not available (You probably messed up the formula)
• #NAME? - Okay, you might have used an undefined range or cell name (easily corrected…).
• #NULL! - You specified an intersection of two areas that do not intersect (Curious error…).
• #NUM! - There is a problem with a number in a formula (Is it really a number?).
• #REF! - An invalid cell reference is mentioned in a function (A cell reference is not valid).
• #VALUE! - The wrong type of argument or operand is used in the formula (Yeah, yeah, yeah)

Now let’s look at a few Handlers you can use to Attack Errors:

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 Perfectly Clear, (as our former President Nixon was fond of saying…), you use IFERROR to define a Custom Response such as “Error in Calculation” or “Problem with 2nd Value”, or something more clever that you invent for your circumstances. In the example shown, the formula would be:

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

By not simply accepting Errors and Error Messages, you can better Take Control of your Excel workbooks. Always a good thing…