Taking Note of Excel Error Values
As irritating as they can be, I actually like seeing Excel errors in my formulas, because it means that something isn’t working and I can fix it. I’d much rather see an error value than a number that looks as though it’s correct when it’s actually completely wrong — that’ll only cause problems later on in the model.
Common sources of errors are parentheses that don’t match or missing fields for functions. Table 13-1 lists some error values you may get and how to fix them.
If you’re sure that the error doesn’t need to be fixed, you can always suppress the Excel errors and stop them from showing by wrapping an IFERROR function around the formula. For example, if you have the formula =(B1-A1)/A1, you can suppress a potential error by adding an IFERROR around it like this: =IFERROR((B1- A1)/A1,0). If you do this, make sure that it doesn’t suppress an error value that you need to know about and correct.
TABLE 13-1 Common Error Values in Microsoft Excel
|
Error Value Description |
|
|
#DIV/0! |
You’re trying to divide by zero. If the divisor is a cell reference, make sure that it isn’t empty and that it doesn’t have a formula with the resulting value of zero. |
|
#NAME? |
There’s a name in the formula that Excel doesn’t recognize. If you used a name you defi check its spelling. You can avoid this error by using the F3 shortcut or selecting a name in the Name Box instead of typing it in. |
|
#REF! |
Your formula refers to a cell that no longer exists, due to a change in the worksheet. This happens if you deleted cells referred to in the formula or pasted cells onto cells referred to in the formula. You need to rebuild the formula. |
|
#VALUE! |
The function you’re using is expecting a numerical value, and you’ve entered text, or vice versa. You can avoid this error by using the Function Arguments dialog box. |
|
Circular Reference |
You’re trying to link a formula to itself somehow. You see Circular followed by a cell reference in the area below the worksheet. Trace back the logic of the formula to correct the error. See Chapter 5 for more information on how to do this. |
Instead of cringing at a horrid #VALUE! error value, or suppressing it with an IFERROR function, take notice of it instead. Figure out what’s wrong, and get to the bottom of it. An Excel error value is far more helpful than the alternative, which is to have deceivingly innocent number values that are, in fact, incorrect.
If you see ###### (known as railroad tracks) in a cell, you may think there’s an error. Good news! This isn’t really an error at all. It just means the result is too long to fit in the cell. Just make the column wider, and — voilà! — the railroad tracks disappear.
Including Error Checks
A good financial modeler is always looking for opportunities to include error checks in the financial model. If you know that the sum total in the CapEx sched- ule should be equal to the sum of each individual item, add an error check to your calculations to check it automatically, so that the user or modeler can see at a glance if the formulas are calculating correctly. You can do this very simply by deducting one value from the other or inserting an IF function. For detailed instructions on how to build error checks, see Chapter 6.
Note that error checks are not a substitute for good practices such as checking and auditing your formulas. Error checks are most appropriate for capturing errors a subsequent user has made. They’re less likely to highlight a model-building error.
