Page 376 - Excel 2013 All-in-One For Dummies

358 Formula Auditing
When Trace Error loses the trail
The Trace Error option finds errors along the path of a formula’s precedents and dependents until it finds either the source of the error or one of the following problems:
✓ Itencountersabranchpointwithmorethan one error source. In this case, Excel doesn’t make a determination on its own as to which path to pursue.
✓ It encounters existing tracer arrows. Therefore, always click the Remove Arrows command button to get rid of trace arrows before you choose the Trace Error option from the Error Checking button’s drop-down menu.
✓ It encounters a formula with a circular refer- ence. (See Book III, Chapter 1 for more on circular references.)
You can see the results in Figure 2-9. Note that Excel has selected cell C12, although cell E12 was active when I selected the Trace Error option. To cell C12, Excel has drawn two blue tracer arrows that identify cells B5 and B9 as its direct precedents. From cell C12, the program has drawn a single red tracer arrow from cell C12 to cell E12 that identifies its direct dependent.
As it turns out, Excel’s Trace Error option is right on the money because the formula in cell C12 contains the bad apple rotting the whole barrel. I revised the formula in cell C12 so that it divided the value in cell B9 by the value in cell B5 without making sure that cell B5 first contained the SUM formula that totaled the values in the cell range B2:B4. The #DIV/0! error value showed up — remember that an empty cell contains a zero value as if you had actually entered 0 in the cell — and immediately spread to cells E12 and E13, which, in turn, use the value returned in C12 in their own calculations. Thus, these cells were infected with #DIV/0! error values as well.
As soon as you correct the problem in the original formula and thus get rid of all the error values in the other cells, Excel automatically converts the
red tracer arrows (showing the proliferation trail of the original error) to regular blue tracer arrows, indicating merely that these restored cells are dependents of the formula that once contained the original sin. You can then remove all the tracer arrows from the sheet by clicking the Remove Arrows command button in the Formula Auditing group of the Ribbon’s Formulas tab (or by pressing Alt+MAA).
Evaluating a formula
The Evaluate Formula command button in the Formula Auditing group of the Ribbon’s Formulas tab (the one with fx inside a magnifying glass) opens the Evaluate Formula dialog box, where you can step through the calculation of a complicated formula to see the current value returned by each part of the