Home > Articles > Home & Office Computing > Microsoft Applications

This chapter is from the book

This chapter is from the book

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

  • yellow-01.jpg Click the cell where you want to enter the function.
  • yellow-02.jpg Click the Formulas tab.
  • yellow-03.jpg Type = (an equal sign).
  • yellow-04.jpg 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.

  • yellow-05.jpg Type a function as an argument to create a nested function, or a regular argument.

    For example, =COUNTIF(E6:E19), “>”&AVERAGE(E6:E19)).

  • yellow-06.jpg Click OK.
  • Conditional Formula Examples



    =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


    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

    • yellow-01.jpg Select the cell with the nested formula you want to evaluate. You can only evaluate one cell at a time.
    • yellow-02.jpg Click the Formulas tab.
    • yellow-03.jpg Click the Evaluate Formula button.
    • yellow-04.jpg Click Evaluate to examine the value of the underlined reference.

      The result of the evaluation appears in italics.

    • yellow-05.jpg 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.

    • yellow-06.jpg Continue until each part of the formula has been evaluated, and then click Close.
    • yellow-07.jpg 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.

  • + Share This
  • 🔖 Save To Your Account