Nested Functions and Formulas in Excel

Lots of Excel experts love to nest functions and formulas inside of each other. This is actually a great way to combine several calculations in one cell, but it can be downright confusing if you are looking at it without understanding the Orders of Operation.

The most important mathematical symbols in Excel are the parentheses. Everything inside of parentheses happens first in any formula, and if there are parentheses inside of parentheses, then you always work from the innermost set of parentheses to find the answer. It’s like Russian nesting dolls, but in reverse. You start with the innermost doll and then work your way up and out to the biggest doll.

Take a look at this formula as an example:

=Concatenate(Proper(M2), “ Total: “, Sum(M3:M8) )

Excel will perform the actions in the formula in this order:

  • Make the value in cell M2 Proper case (first letter capital, all else lower case).
  • Add up the values (sum) in cells M3 through M8.
  • Construct a text string that says July Total: $1251 through the concatenate function.

In addition to functions, where they are required, parentheses are required to make many math formulas work in Excel. Take a look at the following two formulas which are trying to find the average of three numbers: 10, 12, and 15.

Incorrect Equation Correct Equation
=10 + 12 + 15/3 =(10 + 12 + 15)/3
Yields: 27 Yields: 12.33

It’s all about where you put the parentheses! Check out the blog soon for a more in-depth look at Orders of Operation.

Facebooktwitterlinkedinmail