You are currently offline, waiting for your internet to reconnect

This article was previously published under Q289245

back to the top

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

- 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.

=(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

- 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.

- 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

back to the top

OfficeKBHowTo inf XL2002 troubleshoot troubleshooter audit

Properties

Article ID: 289245 - Last Review: 01/31/2007 19:03:25 - Revision: 1.3

- Microsoft Excel 2002 Standard Edition

- kbhowto kbhowtomaster KB289245