Nested Functions in Excel 2007

Nested functions use a function as one of the arguments of another function. You can nest up to 64 levels of functions in Excel 2007. In Excel 2003 you could nest only uto 7 levels under normal working conditions. The following formula sums a set of numbers (D2:D6) only if the average of another set of numbers (C2:C6) is greater than 100. Otherwise it returns 0 or blank.

=IF(SUM(C2:C6)>100,AVERAGE(D2:D6),0)
The AVERAGE and SUM functions are nested within the IF function.

Nested If functions have proven to be useful in many situations.

The function '=LEFT(B1,FIND(" ",B1)-1)'  extracts the first name from a full name by nesting the 'find' function in the 'left' function. The full name is in cell or range B1. First the find function detects the blank space. Now the left function extracts the first name including the space and then '1' is subtracted from the total length extracted by the left function to give us the first name without the space.