- Introduction
- Understanding Formulas
- Creating a Simple Formula
- Creating a Formula Using Formula AutoComplete
- Editing a Formula
- Understanding Cell Referencing
- Using Absolute Cell References
- Using Mixed Cell References
- Using 3-D Cell References
- Naming Cells and Ranges
- Entering Named Cells and Ranges
- Managing Names
- Simplifying a Formula with Ranges
- Displaying Calculations with the Status Bar
- Calculating Totals with AutoSum
- Performing One Time Calculations
- Converting Formulas and Values
- Correcting Calculation Errors
- Correcting Formulas
- Auditing a Worksheet
- Locating Circular References
- Performing Calculations Using Functions
- Creating Functions
- Creating Functions Using the Library
- Calculating Multiple Results
- Using Nested Functions
- Using Constants and Functions in Names

## Using Nested Functions

A nested function uses a function as one of the arguments. Excel allows you to nest up to 64 levels of functions. Users typically create nested functions as part of a conditional formula. For example, IF(AVERAGE(B2:B10)>100,SUM(C2:G10),0). The AVERAGE and SUM functions are nested within the IF function. The structure of the IF function is IF(condition_test, if_true, if_false). You can use the AND, OR, NOT, and IF functions to create conditional formulas. When you create a nested formula, it can be difficult to understand how Excel performs the calculations. You can use the Evaluate Formula dialog box to help you evaluate parts of a nested formula one step at a time.

**Create a Conditional Formula Using a Nested Function**

- Click the cell where you want to enter the function.
- Click the
**Formulas**tab. - Type = (an equal sign).
- Click a button from the Function Library with the type of function you want to use, click a submenu if necessary, and then
click the function you want to insert into a formula.
For example, click the Logical & Reference button, and then click COUNTIF.

Excel inserts the function you selected into the formula bar with a set of parenthesis, and opens the Function Arguments dialog box.

- Type a function as an argument to create a nested function, or a regular argument.
For example, =COUNTIF(E6:E19), “>”&AVERAGE(E6:E19)).

- Click
**OK**.

**Evaluate a Nested Formula One Step at a Time**

- Select the cell with the nested formula you want to evaluate. You can only evaluate one cell at a time.
- Click the
**Formulas**tab. - Click the
**Evaluate Formula**button. - Click
**Evaluate**to examine the value of the underlined reference.The result of the evaluation appears 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.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, and then click
**Close**. - To see the evaluation again, click
**Restart**.Some parts of formulas that use IF and CHOOSE functions are not evaluated, and #NA is displayed. If a reference is blank, a zero value (0) is displayed.