Microsoft KB Archive/289245

= HOW TO: Use the Excel Formula Evaluator in Excel 2002 =

Article ID: 289245

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q289245



IN THIS TASK
SUMMARY
 * How Microsoft Excel Evaluates Formulas
 * Use the Excel Formula Evaluator
 * Observe Formula Evaluation
 * Notes

REFERENCES



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.</li></ol>

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: <ol> In the worksheet that you created in the preceding steps, type the following data in cell B5:

=RAND+B4

</li> 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.</li> 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.</li> Click Step Out and notice that the result of the formula in B4 is now displayed in the Evaluation box.</li> Click Close.</li></ol>

back to the top