Excel nested functions

Once you've learned the basics of Excel functions, it's time to move on to a more complex topic: nested functions. Nested functions are formulas that use one function — like COUNT, for example — as an argument of another function — like SUM. With nested functions, you can create complex chains of formulas that perform customized calculations.

Recall from our explanation of functions in Excel that a function is like a recipe: it contains a function name, which is a unique label used to identify it; arguments, which are like ingredients; and an output, which is the finished product. In this context, a nested function is like a recipe that uses another recipe as one of its ingredients. Take, for example, a recipe for chicken parmesan, which uses marinara sauce as an ingredient. The marinara sauce is an ingredient of the chicken parmesan recipe, but it is also a unique recipe in and of itself, with its own unique ingredients.

Working with nested functions

To nest functions within Excel, simply include the formula for one function as the argument to another. Recall that the formula for a function looks something like this:

=FUNCTION_NAME(argument_1, argument_2, argument_3)
Output: Output

A nested, function, then, would look like something along the lines of this:

=FUNCTION_2_NAME(FUNCTION_NAME(argument_1, argument_2, argument_3), argument_2, argument_3)
Output: Output

Notice that we've inserted one function directly into another as an argument, but kept the parentheses () around argument sets to tell Excel where each function starts and ends. Excel evaluates these functions from the inside out, first calculating the results of the innermost function and then using those results to feed the outermost function.

Let's take a look at a practical example using the SUM and AVERAGE functions:

=SUM(3, AVERAGE(7, 9), 15)
Step 1: =SUM(3, 8, 15)
Output: 26

First, this formula takes the AVERAGE of 7 and 9, coming out with a value of 8. That value is then used as part of the SUM function, which sums up the numbers 3, 8 (the output of our AVERAGE), and 15 to get its final output: 26.

Even more layers of nesting

You can nest almost as many functions within each other as you'd like: Excel offers support for up to 64 layers of nested functions. This can lead to complex, layered formulas that are incredibly powerful, but sometimes difficult to decrypt. See whether you can figure out why the below multi-nested formula outputs what it does:

=SUM(SUM(AVERAGE(1, 3), AVERAGE(5, 7)), SUM(12, 10))
Output: 30

Here's the full breakdown, for those looking for the answer key:

=SUM(SUM(AVERAGE(1, 3), AVERAGE(5, 7)), SUM(12, 10))
Step 1: =SUM(SUM(2, 6), SUM(12, 10))
Step 2: =SUM(8, 22)
Output: 30

When to use nested functions

Nested functions have many uses, and you're sure to come up with interesting and inventive ways of stringing them together that we could never dream of. That having been said, here are some of the most common uses of nested functions:

• Using nested logical functions and IF statements for formulas that deal with a variety of different conditions;
• Using lookup functions nested within each other; and
• Using multiple string functions to parse a complex phrase.

As you read the tutorials throughout the rest of this site, be sure to think of ways that you could combine the functions you learn about in new, powerful ways.

Comments