A cell introduces functions using an equals sign. Though only one equals sign can be used within a cell, several functions are supported. You chain several functions in a single cell through “nesting," a process that places functions as an argument within another function, up to 64 levels deep.
How Nesting Works
To nest a function, begin with the very smallest part of your task -- if you want to get the sum of two averages, for example, you’ll know that you must first calculate those averages. These functions would read a “AVERAGE(A1:A15)” and “AVERAGE(B1:B15)” for example. They would then be nested as comma-delimited arguments within the sum() function, creating a formula that looks like “=SUM(AVERAGE(A1:A15),AVERAGE(B1:B15))”. This sequence can also be inverted, where you average the sums of different columns by typing "=AVERAGE(SUM(A1:A15),SUM(B1:B15))". Each set of parenthesis represents a level -- the previous examples are two levels deep.
The If() Function
If you only want to conduct the aforementioned formulas when (for example) cell X2 is greater than five, you'd use the "IF" function, creating a formula that looks like "=IF(X2>5,AVERAGE(SUM(A1:A15),SUM(B1:B15)),AVERAGE(SUM(A16:A30),SUM(B16:B30)))". In this three-level instance, the "IF" function runs two different average functions which use two different sums, depending on whether or not cell X2 is greater than five. Microsoft Excel allows maximum nested function depth of 64 levels since Excel version 2007.
- Stockbyte/Stockbyte/Getty Images