HOW TO: Use the Excel Formula Evaluator in Excel 2002 (289245)

SUMMARY

This step-by-step article explains how to use the new Formula Evaluator feature in Microsoft Excel 2002. When a cell contains a complex formula, you may find it difficult to determine whether or not it returns the correct value, and if not, which part of the formula needs to be changed. The Formula Evaluator feature, which is new in Excel 2002, aids you in this process.

back to the top

How Microsoft Excel Evaluates Formulas

Excel follows the standard arithmetic precedence of operations, in which multiplication and division operations are performed before addition or subtraction operations. Other operations, such as exponentiation, have a higher priority than either multiplication or division. On the other hand, a comparison operation has a lower priority than either addition or subtraction. When an expression contains two operations of the same priority, such as multiplication and division, the operations are carried out as they appear in the formula from left to right.

Excel evaluates expressions in parentheses before any other evaluation takes place, so you can enclose a lower-priority operation like addition in parentheses to force Excel to evaluate it first. Parentheses can be nested; therefore, if an expression inside the parentheses includes yet another set of parentheses, the innermost expressions will be evaluated first.

For example, if you wanted to add the values in cells B2 and B3, and then divide their sum by their product, you might select cell B4 to display the result, and then incorrectly type =B2+B3/B2*B3 in that cell. If you use familiar values, you can easily see that the result is incorrect, but with unfamiliar values, and especially with more complex formulas, the errors may not be obvious.

back to the top

Use the Excel Formula Evaluator

To use the Formula Evaluator, follow these steps:

Start Excel. In a blank worksheet, type the following data:
```
    B2:    10
    B3:   100
    B4:   =B2+B3/B2*B3
						
```
Notice that the result, 1,010, is not the sum of the two values (110) divided by their product (1,000).
On the Tools menu, point to Formula Auditing, and then click Evaluate Formula.
In the Evaluate Formula dialog box, notice the formula in the Evaluation box, and then click Evaluate.
Repeat step 3 twice and notice that each time the cell reference is replaced by the constant value in that cell.
Click Evaluate again, and then notice that on this occasion, the division is performed before the final cell reference is evaluated.
Click Evaluate three more times to complete the evaluation process. Notice the result of each step.

The Formula Evaluator shows you how the existing formula is being evaluated, but it does not suggest corrections. In this case, it is up to you to ensure that the addition takes place first and the division last by the appropriate use of parentheses. To see the effect of that change, revise the formula in cell B4 to the following:

=(B2+B3)/(B2*B3)

Notice the change in the result, and then repeat the preceding steps to see the difference in evaluation.

back to the top

Observe Formula Evaluation

The Formula Evaluator allows you to observe the evaluation of cells that contain other formulas as well as constants. To do this, follow these steps:

In the worksheet that you created in the preceding steps, type the following data in cell B5:
=RAND()+B4
Start the Formula Evaluator, and then click Evaluate.

Notice the comment in the Evaluate Formula dialog box that states that because the value returned by the RAND() function is not the same on every occasion, interim values in the Formula Evaluator may also vary, although the final result will match the value on the worksheet.
Click Evaluate twice more, and then when you see the cell reference B4 underlined, click Step In.

Notice that you can now see the formula in cell B4 in a separate box, and that you can evaluate it as you did earlier.
Click Step Out and notice that the result of the formula in B4 is now displayed in the Evaluation box.
Click Close.

back to the top

Notes

As the Formula Evaluator processes an expression, it changes the active cell in the worksheet. This means that if you close the Formula Evaluator before you complete the operation, the active cell will not necessarily be the one that contains the formula that you were evaluating. The active cell will be the last cell evaluated in the auditing process.
The Formula Evaluator does not allow you to reverse the process of evaluation; there is no Back button. However, you can repeat the evaluation as many times as you want by clicking Restart when the evaluation has been completed.
You can use the Formula Evaluator to review formulas that refer to cells in other worksheets and in other workbooks. The only difference in referring to cells in other worksheets in the same workbook is that the cell reference includes the name of the worksheet, as it does on the worksheet itself.

In the case of cells in other workbooks, there is a limitation in that you cannot step into formulas contained in cells in other workbooks. To audit formulas in those cases, you need to open the workbook in question and use the Formula Evaluator directly.
Formulas that can be evaluated in different ways depending on input, such as those using the IF or CHOOSE functions, will be evaluated according to the current input, and the inactive paths will return the following result:
#N/A