In Excel, you can actually place (or nest) a function within a function. Look at the following worksheet as an example.

 

 

By looking in the formula bar, you can see that the average function for cell F1 contains three sum functions (the sums of the three category columns). This means that the value in F1 is the average of the sums of columns B, C, and D.

 

The following function is also possible.

 

=AVERAGE(SUM(B2:B6),SUM(C2:C6),SUM(AVERAGE(B2:B6),AVERAGE(C2:C6),AVERAGE(D2:D6)))

 

Notice that this function has 3 average functions, nested within a sum function, which is in turn nested in another average function. This may seem confusing, but if you carefully step through the function, from right to left, it becomes clear. The average of range D2:D6, the average of C2:C6, and the average of B2:B6, are summed together. This sum is then averaged with the sum of C2:C6 and the sum of B2:B6 for a final result.

 

In terms of nested functions, nesting averages within sums, and sums within averages is probably not that practical. However, nested IF functions can be extremely useful in a wide variety of situations.

 

=IF(A1<100, A1*1.5,IF(A1<200, A1*2,IF(A1<300, A1*2.5,A1*3)))

 

This IF function, has two additional IF functions nested inside. Start at the left, and examine each IF statement carefully. The first IF tests to see whether the value in cell A1 is less than 100. If it is, the result of this function will be the value of A1 multiplied by 1.5. If the value in A1 is equal to or greater than 100, the test condition will be false, and the second value will come into play. In this case, the second value is a nested IF function that tests whether the value in A1 is less than 200. If it is, the value in A1 multiplied by 2 will be the result of the function. If the value in A1 is greater than or equal to 200, the third IF function will be used. This function tests whether the value in A1 is less than 300. The value in A1 will be multiplied by 2.5 if it is less than 300, and multiplied by 3 if it is greater than or equal to 300.

 

In the practice exercise, you will develop this kind of nested IF function, and apply it in a practical way. Remember, when nesting functions always make sure that there are as many closing parentheses as there are opening parentheses. ((( )))