Microsoft Office Tutorials and References

In Depth Information

**Checking for Errors Automatically One at a Time**

Evaluating a Formula

Another way in which you can determine the

problem with a formula is to evaluate it.

Evaluating is especially helpful when trying to

troubleshoot complex formulas with lots of

parentheses, because evaluating allows you to

review exactly how Excel is solving each step of

the formula. As you evaluate a formula, Excel

solves the formula’s first step and displays that

intermediate result. When you’re ready, Excel

continues, solving the next step, and so on,

until the formula is solved and the final result

is displayed.

3.
Click the Evaluate button. Excel solves the

first step in the formula, which is underlined.

In this case, the formula is

=IF($A16=“”,“”,VLOOKUP($A16,Products,3)).

Excel is moving left to right in this formula,

so it looks up the value in cell A16 in

preparation for comparing whether A16

equals “” (blank). So after you click Evaluate,

in the Evaluation box, Excel displays

IF(BL105=“”,“”,VLOOKUP($A16,Products,3)).

Tip

If the underlined reference in the formula

is a reference to another formula, you can

jump to that other formula and evaluate

it step by step by clicking the Step In

button. Click Step Out after evaluating

this second formula, to return to the first

formula to finish its evaluation.

Follow these steps to evaluate a formula:

1.
Click a cell containing a formula.

2.
Click the Evaluate Formula button on the

Formulas tab. The Evaluate Formula window

appears, as shown in Figure 4-7.

Actual

Formula

Evaluation of

Formula

4.
Click the Evaluate button again to solve the

next step in the formula. See Figure 4-8.

In this next step, the formula appears as

IF(FALSE,“”,VLOOKUP(($A16,Products,3))

because the value in cell A16 is not blank.

Figure 4-8

After you click Evaluate again, Excel solves

the next step in the formula.

Figure 4-7

Evaluate a complex formula step by step.