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

    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

    • 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