Sometimes, understanding how a nested formula calculates the final result is difficult because there are several intermediate calculations and logical tests. However, by using the Evaluate Formula dialog box, you can see the different parts of a nested formula evaluated in the order the formula is calculated. For example, the formula =IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) is easier to understand when you can see the following intermediate results:
|
Steps shown in the dialog box |
Description |
|---|---|
|
=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) |
The nested formula is initially displayed. The AVERAGE function and the SUM function are nested within the IF function. |
|
=IF(40>50,SUM(G2:G5),0) |
The cell range F2:F5 contains the values 55, 35, 45, and 25, and so the result of the AVERAGE(F2:F5) function is 40. |
|
=IF(False,SUM(G2:G5),0) |
40 is not greater than 50, so the expression in the first argument of the IF function (the logical_test argument) is false. |
|
0 |
The IF function returns the value of the third argument (the value_if_false argument). The SUM function is not evaluated because it is the second argument to the IF function (value_if_true argument) and it is returned only when the expression is true. |
-
Select the cell that you want to evaluate. Only one cell can be evaluated at a time.
-
On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.
-
Click Evaluate to examine the value of the underlined reference. The result of the evaluation is shown in italics.
If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula.
Note: The Step In button is not available for a reference the second time the reference appears in the formula, or if the formula refers to a cell in a separate workbook.
-
Continue until each part of the formula has been evaluated.
-
To see the evaluation again, click Restart.
To end the evaluation, click Close.
Notes:
-
Some parts of formulas that use the IF and CHOOSE functions are not evaluated, and #N/A is displayed in the Evaluation box.
-
If a reference is blank, a zero value (0) is displayed in the Evaluation box.
-
Formulas with circular references may not evaluate as expected. If circular references are desired, you can enable iterative calculation.
-
The following functions are recalculated each time the worksheet changes, and can cause the Evaluate Formula tool to give results different from what appears in the cell: RAND, OFFSET, CELL, INDIRECT, NOW, TODAY, RANDBETWEEN, INFO, and SUMIF (in some scenarios).
-
