- 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
- Calculating Totals with Quick Analysis
- 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.
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.
Conditional Formula Examples
Formula |
Result |
|
=AND(A2>A3, A2<A4) |
If A2 is greater than A3 and less than A4, then return TRUE, otherwise return FALSE |
|
=OR(A2>A3, A2<A4) |
If A2 is greater than A3 or A2 is less than A4, then return TRUE, otherwise return FALSE |
|
=NOT(A2+A3=24) |
If A2 plus A3 is not equal to 24, then return TRUE, otherwise return FALSE |
|
IF(A2<>15, “OK”, “Not OK”) |
If the value in cell A2 is not equal to 15, then return “OK”, otherwise return “Not OK” |
Evaluate a Nested Formula One Step at a Time
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.