learn microsoft excel

Nested Functions in Excel 2007

Home Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Blog Excel Books


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.

Watch the training video below to see how nested function in Excel are implemented.
Nested Functions Excel

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.


Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Blog Excel Books