Microsoft Office Tutorials and References

In Depth Information

**Solving Formula Errors**

Solving Formula

Errors

Excel’s solution is to provide a feature—called the Evaluate Formula tool—that

evaluates your formula one sub-expression at a time. Using this tool, you can watch as

Excel computes your formula, up until the point where the error appears.

To watch the step-by-step execution of a formula that contains an error, follow these

steps:

1. Movetothecellthatcontainstheformulathat’sproducingtheerror.

You don’t need to highlight the formula, you just need to be in the offending

cell.

2. ChooseFormulas
➝
FormulaAuditing
➝
EvaluateFormula.

The Evaluate Formula dialog box appears (Figure 18-1), with the formula in a

large, multiline textbox.

Figure 18-1:

The first two arguments in this formula

have been evaluated. (The second

argument [60] is italicized, indicating

that Excel calculated it in the last step.)

The next time you click Evaluate, Excel

evaluates the third argument, which

is underlined. If you want to show the

contents of this cell before evaluating

it, you can click Step In.

3. Excelunderlinesthepartoftheformulathatit’sabouttoevaluate.Clickthe

Evaluatebutton.

Excel evaluates the sub-expression and replaces it with the calculated value. 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. Repeatstep3untilthesub-expressionthatgeneratestheerroroccurs.

When the error occurs, you’ll see the error code appear in your formula. When

you click Evaluate again, the error code spreads, encompassing the whole

expression or the function that uses it. Consider the ill-fated formula =
5/0
. The
+

first step (the division) creates a divide-by-zero error, and the formula appears

as =
#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.