 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 3D 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.