Page 178 - Excel Timesaving Techniques for Dummies

33_574272 ch29.qxd 10/1/04 10:50 PM Page 163
163
Finding the Original Error and Fixing Its Formula
Ignore Error: Click this button to have the pro- the Error Checking dialog box. If the program then
gram disregard the error value and pass on to finds no other error values in the worksheet, an alert
the next error value in the worksheet. dialog box appears, indicating that the error check
of the worksheet is complete. Click OK to close both
Edit in Formula Bar: Click this button to activate
this alert dialog box and the Error Checking dialog
the Formula bar so that you can edit the formula
box simultaneously. Finally, you can remove all the
and fix the problem.
tracer arrows from the sheet by clicking the Remove
All Arrows button on the Formula Auditing toolbar
and close the toolbar by clicking its Close button.
Unfortunately, the Error Trace feature is not infalli-
ble. Sometimes this feature can’t find the source of a
formula error the first time you use it. Trace Error
will fail to locate the source of the error if the pro-
gram encounters one of the following conditions in
its search for the current cell’s precedents and
dependents:
• Figure 29-8: Correcting the formula error in the Error
Checking dialog box. A branch point with more than one error
source: In this case, Excel doesn’t make a deter-
If you decide to use the Edit in Formula Bar button, mination on its own as to which path to pursue;
make your changes to the formula on the Formula you have to inspect each path manually.
bar and then click the Enter box on the Formula bar
Preexisting tracer arrows: Always click the
to enter your fix into the cell. You can tell if you cor-
Remove All Arrows button to remove all preexist-
rected the problem because the calculated result ing trace arrows before you click the Trace Error
will replace the error value in the cell and Excel will button.
remove all the error values in the other dependent
A formula containing a circular reference: You
cells. Also, the program converts all the red tracer
arrows (showing the proliferation trail of the original need to try to resolve the circular reference by
recalculating the worksheet by selecting the
error) to regular blue tracer arrows, indicating
Iteration check box on the Calculation tab of the
merely that these restored cells are dependents of
Options dialog box and then increasing the num-
the formula that once contained the original error.
ber of iterations in the Maximum Iterations text
You can then click the Resume button (which auto- box and perhaps decreasing the amount of
change in the Maximum Change text box.
matically replaces the Help on this Error button) in