Microsoft Office Tutorials and References

In Depth Information

**Auditing and documenting worksheets**

Evaluating and auditing formulas

Sometimes it’s difficult to tell what’s going on in a complex nested formula. A formula

is
nested
when parts of it (called
arguments
) can be calculated separately. For example,

in the formula =IF(Pay_Num<>"",Scheduled_Monthly_Payment,""), the named reference

Pay_Num
indicates a cell that must contain a value in order for the rest of the formula to

function. To make this formula easier to read, you can replace this expression with a

constant—in this case, 1 (indicating that the expression is TRUE). The formula would then be

=IF(1<>"",Scheduled_Monthly_Payment,"").

When you click the Evaluate Formula button on the Formulas tab, you can resolve each

nested expression one at a time in complex formulas. Figure 8-59 shows the Evaluate

Formula dialog box in action.

Figure 8-59
Click the Evaluate Formula button on the Formulas tab to systematically inspect

nested formulas.

For more information about formulas, named references, and arguments, see Chapter 12.

Click Evaluate to replace each calculable argument with its resulting value. You can click

Evaluate as many times as necessary, depending on how many nested levels exist in the

selected formula. For example, if you click Evaluate in Figure 8-59, Excel replaces the

aforementioned Pay_Num reference with its value. Clicking Evaluate again calculates the next

level, and so on, until you reach the end result, which in this case is $188.71, as shown in

Figure 8-60.

You’ll find the Loan Amortization.xlsm file with the other examples on the companion

website.