Excel > Excel 2007 Intermediate > More Advanced Functions

Nested Functions

Extremely powerful formulas can be created by nesting functions within other functions. It is possible to nest up to 7 layers of functions.

Nesting functions is essentially placing a function into the argument of another function.

A common example of nesting functions, mentioned in a previous tutorial, is to nest the AND and OR functions within the IF function. This is done to test multiple conditions as the IF function alone can only perform one logical test.

The example below uses the AND function to test the exam and coursework grades. The AND function tests both conditions and passes the result (True or False) to the IF function. The IF function will then display Pass or Fail.

Nesting with the And function

The result is Fail because the coursework grade in cell C4 is less than 70% and the AND function will only result in True if both conditions are met.

Remember the golden rule when writing functions, for every opening parenthesis (, you need a closing parenthesis ). Put another way, you need to close every function that you open.

Let's take it a step further and include the OR function.

In the example below OR function uses the AND function as one of its arguments. The AND function runs first resulting in False. It passes this to the OR function which checks its arguments and results in True. This is then finally passed to the IF function, which displays Pass.

Nesting with the Or function

The last nested function example illustrates nesting functions into the Value if false argument of the IF function to display multiple results. The example below tests only one condition to make things simpler, but could easily be combined with the functions used earlier to test multiple conditions.

The function displays the result of A, B, C or D dependent upon the exam grade. The order that the function is written is of upmost importance here. Nested functions run from the deepest level outwards. For the function to work it needs to test for grade A last, therefore this is tested in the outermost IF function.

If function with multiple results

Notice the 3 closing parentheses at the end of the function. This is a fantastic example of the golden rule of entering a closing parenthesis for every opening parenthesis.

Click on the related tutorial links below for more examples of using nested functions. They really do harness the raw power of Excel producing some amazing results.

Follow us on

Facebook  Twitter  You Tube