12.5. Solving Formula Errors
Errors...they occur in the most unexpected places, transforming rows of calculations into unhelpful error codes like # NAME ? , #VALUE! , and # MORON! (OK, that last one doesn't actually appear in Excel, but it might as well given the sense of defeat and frustration these error codes can leave you with.) In some cases, you can see how to fix an error just by looking at the formula. However, sometimes the problem isn't so easy to solve, especially if your formulas perform calculations using the results of other formulas. In such cases, it can be tough to track down where the original error occurred.
Excel provides some interesting f ormula auditing tools a handful of features that inspect broken formulaswhich can make it much easier to fix errors. With any error, your first step is to identify the error code by using the information listed on Table 7-2. If the problem isn't immediately obvious, you can use the Formula Auditing tools to perform the following tasks :
Evaluate an expression step-by-step, until you hit the error. That way, you know exactly what part of the formula is causing the error.
Trace the precedents of a formula that's causing an error. Precedents are the cells that a particular formula references. In the formula =A1+B1 , both A1 and B1 are precedents. If either one of these cells contains an error, the error gets fed intoand trips upthe formula.
Trace the dependents of a cell. Dependents are other cells that use the current cell. For example, if one cell has the formula =A1+B1 , and another cell contains =A1*10 , both these cells are dependents on cell A1. If A1 has an error, it infects both formulas.
Perform an error check on the entire worksheet. Excel's error check is similar to a spell check. One by one, it takes you to each cell that has an unresolved problem.
To perform any of these tasks, you'll need the Formula Auditing toolbar (Figure 12-11). (These features are also available, a little less conveniently, through the Tools Formula Auditing menu.) You can show this toolbar by selecting View Toolbars Formula Auditing. When the toolbar first appears, it floats above your worksheet. However, you can dock it to any side of the Excel window that's convenient for you.
|
Complex formulas usually include multiple sub-expressions . Each sub-expression is a piece of any formula that's evaluated separately. It may be an arithmetic operation in parentheses, a nested function, or even just a cell reference. In order to understand what's causing an error in your formula, you need to know which sub-expression caused the problem.
Excel's solution is to provide a featurecalled the Evaluate Formula toolthat evaluates your formula one sub-expression at a time. Using this tool, you can watch as your formula is computed, up until the point where the error occurs.
To watch the step-by-step execution of a formula that contains an error, follow these steps:
Move to the cell that contains the formula that's producing the error.
You don't need to highlight the formula, you just need to be in the offending cell.
On the far right side of the Formula Auditing toolbar, click the Evaluate Formula button (or select Tools Formula Auditing Evaluate Formula).
The Evaluate Formula dialog box appears (Figure 12-12), with the formula in a large, multiline textbox.
Excel underlines the part of the formula that it's about to evaluate. Click the Evaluate button.
Excel evaluates the sub-expression and replaces it with the calculated value. For example, it might replace a cell reference with the cell's actual value, evaluate an arithmetic operator, or execute a function. The value appears in italics, indicating that it's the most recent value that Excel has calculated.
Repeat step 3 until the sub-expression that generates the error occurs.
When the error occurs, you'll see the error code appear in your formula. When you click Evaluate again, the error code will spread, encompassing the whole expression or the function that uses it. For example, consider the ill-fated formula =1+5/0 . The first step (the division) creates a divide-by-zero error, and the formula appears as =1+#DIV/0! . But you can't add 1 to an error, so in the next step the error spreads to the whole formula, which becomes =#DIV/0! in the end.
|
When the calculation process ends, you can click Restart to repeat the calculation from the beginning.
You can also click Close to stop evaluating the formula and return to your worksheet at any time.
The Evaluate Formula dialog box is one way of examining the anatomy of complex formulas. However, depending on the complexity of your formulas, you can end up having to move through a long series of steps before you find the problem. In this case, you might be interested in using a different approach, one that uses Excel's ability to trace linked cells graphically. This feature isn't any better or worse than the Evaluate Formula dialog boxit's just another tool that you can use to resolve problems depending on the situation and your own personal preference.
First, a quick review of how Excel thinks about precedents and dependents. Consider the following formula:
=A1+B1
If this formula is in cell C1, that makes A1 and B1 precedents of C1. In other words, C1 relies on the values in A1 and B1 in order to do its work. If either of these cells contains an error value, the problem will spread into C1. Another way to express this relationship is to say that C1 is the dependent of both A1 and B1.
Excel's tracing features let you see a graphical representation of these relationshipsin the form of blue arrowsright on your worksheet without needing to look in another window or dialog box.
Gem In the Rough Digging Deeper into Linked Formulas |
Even when you've discovered the sub-expression that's causing the trouble, you still may not have found the root of the problem. If the sub-expression that's causing the error is a reference, it might point to another cell that contains another formula. If it does, you'll need to evaluate that formula step-by-step in order to find the real mistake. To evaluate the second formula, you can move to the appropriate cell and start the step-by-step evaluation process by clicking the Evaluate Formula button. However, Excel also provides a useful shortcut that lets you jump from one formula into another. The secret is the Step In and Step Out buttons in the Evaluate Formula box (see step 2 on Section 12.5.1).
When you're using this box, the Step In button becomes available just before you evaluate a sub-expression containing a cell reference. If you click the Step In button at this point, Excel adds a new text box to the dialog box underneath the first one. This new text box shows the contents of the referenced cell. Excel also informs you if the cell contains a formula or a constant (just read the label at the bottom of the Evaluate Formula dialog box). If the cell contains a constant, there's no calculation left to perform, and you'll need to click Step Out to return to the original formula. If the cell does contain a formula, you can click the Evaluate button to start evaluating itone sub-expression at a timeand then click Step Out once you're finished. In fact, Excel lets you dig even deeper into chains of linked formulas. Every time you find a cell reference that points to another formula-holding cell, you can click Step In to show the formula in a new text box. You can continue this process with no practical limit. If you exceed the space available in the Evaluate Formula dialog box, Excel just adds a scroll bar to help you out. The figure here shows an example where the Evaluate Formula dialog box has drilled down an impressive 13 levels! In this chain, cell B13 references B12, which references B11, and so on, all the way to B1. |
To see tracing in action, move to a cell that contains one or more cell references, then click the Trace Precedents button on the Formula Auditing toolbar (or select Tools Formula Auditing Trace Precedents). Excel displays solid blue arrows that link the cells together. For example, if you click Trace Precedents in the cell C1 that contains that formula =A1+B1 , you'll see two arrows. One points from A1 to C1, and the other points from B1 to C1. Figure 12-13 shows an example.
|
The first time you click Trace Precedents, you see the direct precedents . These are the cells that are directly referenced by the current formula. However, these precedents may themselves refer to other cells. To see these cells, click Trace Precedents again.
There's no limit to how many times you can click Trace Precedents. As long as there are more indirect precedents, Excel will continue adding arrows. At any point, you can remove a single level of arrows by clicking the Remove Precedent Arrows button, or you can clear everything by clicking the Remove All Arrows button (both buttons are on the Formula Auditing toolbar).
You can trace dependents in the same way that you trace precedentsjust click the Trace Dependents button (see Figure 12-14). For example, if you click Trace Dependents and cell A1 is selected, Excel adds an arrow connecting A1 to any other cells that refer to A1.
|
There really isn't a difference between precedent and dependent arrowsthey're just two different ways of looking at the same idea. In fact, every arrow Excel draws connects one precedent to one dependent. Finally, Excel's tracing tools also work with formulas that aren't working (which is important, after all, when it comes to troubleshooting). Figure 12-15 shows how the tool works when your formulas are generating error codes.
Sometimes, you might have a large worksheet that contains a number of errors that are widely distributed. Rather than hunt for these errors by scrolling endlessly, you can jump straight to the offending cells using Excel's error-checking feature.
To perform an error check, follow these steps:
Move to the position where you want to start the error check.
If you want to check the entire worksheet from start to finish, click the first cell. Otherwise, go to the location where you want to start checking. As with a spell check, Excel moves from column to column first, and then from row to row. However, the error checker automatically loops back to the beginning of your worksheet, making sure to check every cell before it stops.
|
Choose Tools Error Checking, or click the Error Checking icon in the Formula Auditing toolbar.
If no errors are found in your worksheet, Excel displays a message indicating that its work is complete. Otherwise, you'll see the Error Checking dialog box, as shown in Figure 12-16, which indicates the offending cell and formula. It also provides a number of options.
|
The Error Checking dialog box contains the following options:
Next or Previous . Use these buttons to move from one error to the next.
Help on This Error . Click this button to jump to Excel's online help, which lists common causes of specific errors. It may give you some insight into your own troubles.
Show Calculation Steps . Use this button to open the Evaluate Formula dialog box, where you can move one step at a time through the evaluation of the formula.
Ignore Error . Click this button to skip the error, and ignore the cell from this point onward. If you want the error checker to pay attention to a cell you've previously decided to ignore, you'll need to click the Options button, and then click Reset Ignored Errors.
Edit In Formula Bar . Use this button to start editing the formula. This choice doesn't close the error checkerinstead, you can click Resume to get back to checking other errors once you've made your change.
Options . Click this button to see another dialog box listing additional error-checking options (see Figure 12-17). (This dialog box provides the same options as you'd see if you selected Tools Options and select the Error Checking tab.) Ordinarily, you won't need to change any of these error checking options, as the factory settings are stringent enough to ensure that all problems are caught.
|