Sometimes, understanding nested formulas can be difficult, especially long and complex formulas! However, by using the **Evaluate Formula** dialog box, you can break down the formula calculation sequence step by step so that you can understand it.

Let's see how to solve the formula `=SUM(IF(B2:B5>AVERAGE(B2:B5),D2:D5,0))`

with the following example:

- Select the cell that you want to evaluate. Only one cell can be evaluated at a time. In this example, we select cell
**F5**. - 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**. - The detailed evaluate steps of the formula are listed below:
`=SUM(IF(B2:B5>AVERAGE(B2:B5),D2:D5,0))`

`=SUM(IF({450;550;720;850}>`

*642.5*,D2:D5,0))`=SUM(IF(`

*{FALSE;FALSE;TRUE;TRUE}*,D2:D5,0))`=SUM(`

*{0;0;108;102}*)`=`

*210*

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