Microsoft Office Tutorials and References

In Depth Information

**Evaluating and Checking Errors**

Evaluating and Checking Errors

The Evaluate Formula dialog box walks you through the sequential steps

used in calculating a result from a formula. These steps are useful in tracking

down errors in formulas that are long or have precedents. For example, the

formula =IF(MAX(Orders!B2:B29)>200,MAX(Orders!B2:B29)*Shippi

ng!C22,Shipping!C24) refers to different worksheets. Using the Evaluate

Formula dialog box makes it easy to see how this formula is worked out by

Excel. The step-by-step approach lets you see what is done at each step.

Figure 4-17 shows the Evaluate Formula dialog box at the start of evaluating

the formula. To display the Evaluate Formula dialog box, simply click the

Evaluate Formula button on the Formulas tab on the Ribbon. With each

successive click on the Evaluate button, the Evaluation box displays the interim

results. The Step In and Step Out buttons are enabled during the steps that

work on the precedents.

Figure 4-17:

Evaluating a

formula.

The Evaluate Formula dialog box is great for really seeing how each little step

feeds into the final calculated result. Using this dialog box lets you pinpoint

exactly where a complex formula has gone sour.

A similar error-hunting tool is the Error Checking dialog box. (Excel really

wants to help you!) Figure 4-18 shows the dialog box.

Figure 4-18:

Checking

the cause of

an error.