Section 12.5. Solving Formula Errors

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.

Figure 12-11. The Formula Auditing toolbar is the starting point for tracking errors and resolving problems. The following sections explain the most important features of the toolbar: its ability to find errors, evaluate formulas piece-by-piece, and trace relationships. The validation circles (the third and fourth buttons from the right) have nothing to do with error tracking, but are for data validation, which you'll explore in Chapter 15.

12.5.1. Step-by-Step Evaluation

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:

  1. 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.

  2. 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.

  3. 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.

  4. 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.

    Figure 12-12. The first two arguments in this formula have been evaluated. (The second argument is italicized, indicating that Excel calculated it in the last step.) The next time you click Evaluate, Excel will evaluate the third argument, which is underlined . If you want to show the contents of this cell before evaluating it, you can click Step In.

  5. 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.

    Tip: Step-by-step evaluation isn't just for solving errors. It can also help you understand why a formula doesn't produce the result you expect. You can use the Evaluate Formula dialog box with a formula that doesn't cause an error in exactly the same way as one that does. By watching the calculation proceed step-by-step, you may realize that the order of operations Excel follows is subtly different than the order you expected. You can then edit the formula accordingly .

12.5.2. Tracing Precedents and Dependents

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:


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.

Figure 12-13. Top : This example shows the direct precedents of cell H2. As you can see, H2 calculates the student's final grade based on the test results that are stored in cells C2 and F2. Because these two arrows overlap, they appear as one arrow, but you can clearly see two circles, each of which contains an arrow end point (one each on cells C2 and F2). Excel also lets you trace multiple levels of relationships (bottom). Just click the Trace Precedents button again to see if the precedent cells have other precedents.
Bottom : Here you can see that the test result cells are themselves calculations that rely on other cells. For example, C2 makes its calculations using cells B2 and B12.

Note: If a formula references a cell in another worksheet or workbook, Excel draws a dotted line linking your cell to a small grid icon. This icon represents the other worksheet or workbook, and you won't be able to see the actual cell that the formula links to.

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).

Tip: There's nothing to prevent you from tracing the precedents for a bunch of different cells: just move to another cell and repeat the process for each cell you want to trace, one after the other. All the arrows will be visible at once, which can make for a tangled worksheet. When you click Remove All Arrows, Excel removes all the precedent arrows and any dependent arrows for every cell you've traced. You can remove the arrows for just one cell by moving to it and clicking Remove Precedent Arrows.

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.

Figure 12-14. If you click Trace Dependents on cell H2, Excel indicates that this cell is used in the average calculation in cell H15. However, it isn't the only value that cell H15 uses. To see all the precedents, you'd need to move to H15, and then click Trace Precedents.

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.

12.5.3. Error Checking

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:

  1. 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.

    Figure 12-15. Excel's tracing features work with any formulaswhether they contain an error or not. But Excel also includes a related feature, Trace Error, which works only with formulas that result in error values. When you select a cell with an error code, and then click the Trace Error button, Excel traces all the precedents that lead back to the error by using blue arrows. Then, Excel uses red arrows to indicate how the error spread. In this example, two blue arrows show the precedents of cell C2, where the error occurred. The error then spread to cell H2 and, finally, to the current cell, H15.

  2. 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.

Figure 12-16. The error checker helps you scan through a worksheet and quickly jump to the cells that contain errors. You can click the Show Calculation Steps button to quickly jump to the Evaluate Formula dialog box and start analyzing the problem.

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.

Figure 12-17. Excel doesn't give you many error options. Background error checking is usually activeit's the feature that flags cells with tiny green triangles in the top-right corner when they have a problem. You can turn off this feature, or change the color of the tiny triangles. The other options focus on specific types of errors. For example, you can choose to have Excel ignore numbers stored as text and other situations that technically aren't errors, but usually indicate that you've done something you didn't mean to. Excel always reports genuine errors, like #VALUE! and #NAME? regardless of what choices you make in this dialog box.

Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: