Microsoft Office Tutorials and References

In Depth Information

**Solving Formula Errors**

Solving Formula

Errors

5. Whenthecalculationprocessends,youcanclickRestarttorepeatthecalcula-

tionfromthebeginning.

You can also click Close to stop evaluating the formula and return to your

worksheet at any time.

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 may point to another cell that contains

another formula. If it does, then you need to evaluate
that

formula step-by-step in order to find the real mistake.

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 or there’s no

calculation left to perform, you 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 it—one

subexpression at a time—and then click Step Out once you’re

finished.

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 dialog box (Figure 18-2).

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.

When you’re using the Evaluate Formula dialog 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

Figure 18-2:

In this example, the Step In button

has taken you three levels deep into

a formula. The formula it’s

evaluating is in the first box; it’s A3+A4+A5.

However, clicking Step In adds a

second box, which reveals that cell

A3 itself contains a formula (B3+C3).

Finally, another click of Step In shows

a third box, which zooms in on the first

part of the second formula (B3), and

shows that the cell it points to holds

the number 84.