Mad Marv’s Place

Common Error Messages

#NUM!
Occurs when the numeric inputs for the formula are unacceptable or cause a problem. For example =SQRT(-1) will cause a #NUM! error because the result is not a real number. =10^500 will also cause a #NUM! error because the result is too large for Excel to handle.

#NAME?
Occurs when the text in the formula is not understood. It could be a typo in the name of a named range of cells or a function like =SQT() instead or =SQRT(), omitting a colon (:) in a cell range, or entering text in a formula without enclosing it in ” “. This error also occurs when using a function that has not been loaded in Excel. This is likely to happen if you use XIRR() without loading the Analysis Toolpak.

#REF!
Occurs when a cell reference is missing because the referenced cell was deleted.

#DIV/0!
Occurs when the formula divides by 0. It usually happens when the formula is something like =A1/B1 and B1 is empty.

#VALUE!
Occurs when the wrong type of input is fed into a formula.

For example, formula is expecting numeric inputs and text is entered. =A1*B1, when the contents of either A1 and B1 is not a number. The exception to this rule is TRUE and FALSE which Excel reads as 1 and 0 respectively.

A range of cells is entered when the formula is expecting a single cell.

Typing an array formula without using Ctrl+Shift+Enter.

Circular References
When one or more formulas reference each other such that a logical loop is formed, Excel will trigger an error message telling you that you’ve created a circular reference. For example, if cell A1 has the formula =B1 and cell B1 has =A1, this will create a circular reference and Excel will ask if you are sure you want to use this formula. Excel will allow circular references if you really want them. Circular references are an infinite loop, but they will not crash Excel because Excel caps the number of loop iterations. There are some useful applications of circular references, they can be used to calculate solutions that can only be found through successive iterations. But more often than not, they are errors in a spreadsheet.

Debugging and the Audit Tool
When writing a spreadsheet, typos inevitably occur. The problem is you might not figure out where they are until after you’re done. The trick to spotting an error in your spreadsheet is to test it using simple data with round figures. The data should be easy enough for you to figure out what the formulas should spit out. For example, if you’re debugging financial ratios from an income statement, input $10 M revenue, $5 M cost of sales, $1 M Net Income, & 2 M shares. You can see that Gross Margin should be 50%, Net Margin 10%, and EPS $0.50. Any errors in the spreadsheet will be easy to pick out.

Once you’ve found a formula that has a bug in it, fixing it is usually pretty simple. But, if the spreadsheet is large enough, it may be difficult to trace the references in a formula when you’re debugging the sheet. To make this easier, Excel has the Audit command. It will draw a line and arrow from the cell you select to all precedents (inputs into the formula) or decendants (all cells that use the selected cell as an input).

Click on the buggy cell and go to Tools –> Auditing –> Trace Precedents. Several lines and arrows will appear on the spreadsheet pointing to the inputs in the formula. Similarly, Tools –> Auditing –> Trace Dependants will trace a path to all formulas that use that cell as an input.

Tags: Investing //