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.
Search JabSto ::




Custom Search