Building Nested Functions Easy
About Nested Functions
In all the years that I have been training Excel Functions, there is a feature that consistently puzzles. I am referring to the creation of nested functions. The problem is not about the concept of nesting an expression inside another one but about the technique used for nesting functions inside one another.
What are nested function again?
A nested function is a function that returns its result to another function one level up. There could be any number of levels. Typically I see three or four level of nesting. Below is a simple example:
In this example the AVERAGE() function calculates a result and passes that result to the function one level up; to the IF() function. If the average of the scores is 82% then the IF() function evaluates this way:
=IF(0.82>0.7,"Great","Hummm!") The result will be: Great.
So what seems to be the problem?
The difficulty if not about understanding how to resolve this type of problem but much more how to build such formula in Excel. I occasionally hear participants that they have handcrafted 6 levels of IF() functions … or more! But it took them more than 30 minutes to do so. The technique discussed in this post will reduce that time by 60% minimum. Below is another classic example using VLOOKUP():
The VLOOKUP pros will quickly recognize this formula solving a weakness in VLOOKUP when it is copied across. But the question remains to know if there is a technique allowing to easily nest a function inside a function at any level without loosing our sanity? Yes there is!
First example of nested function
Let’s examine an example of formula that uses nested functions and do it together.
If a sales occurs in Melbourne or Adelaide then a discount of certain percentage located in cell E2 is applied to the total otherwise no discount is applied. Suppose the city is in A2 and the amount is in B2 then the formula in C2 would be as follow:
Follow the steps below:
- In cell C2 type =IF then hit the Tab key and hit Ctrl+A to open the IF() Function box.
- Because the Logical_test is one of two cities type OR() in that box.
- The Formula Bar contains: =IF(OR()) so click anywhere inside the function name OR(). Now you see the OR() Function box. Click in the Logical1 box.
- In the Logical1 click cell A5 then type =”Melbourne”. The expression is now A5=”Melbourne”. In the second box click A5 again but now type =”Adelaide”.
The OR() function can hold up to 255 conditions. Way more than you’ll ever need!
- Now click in the Formula Bar inside the function name IF() to move one level up back to the IF() function. Click in the Value_if_true box.
- While in the TRUE argument box click cell B5 and type *(1-E$2). The complete expression is: B5*(1-A$2). Hit Tab to move to the third box.
- In the FALSE argument box simply click cell B5. Your Function Arguments box should look as shown here.
Click OK then double-click the Fill Handle to copy your formula down. The formula is copied to the other cities. If you examine the Totals the amounts for Melbourne and Adelaide are slightly smaller than the the Amount. They are exactly 4% lower. If you change the percentage in cell A2 the two totals will update.
As you have seen in this first example, to nest a function inside another function simply type the name of the function with the brackets, click the function name in the Formula Bar to bring up the Function Arguments for that function then click in the first argument. Always keep an eye in your Formula Bar to ensure you know what level you’re in.
To re-open the Function Argument box for a complex function you previously created you can use two simple ways.
- Click the cell containing the formula then click the fx button to the left of the Formula Bar or press Shift+F3.
- Click inside a nested level of your formula in the Formula Bar (in our example click somewhere in the OR level) then click the fx button to the left of the Formula Bar or press Shift+F3.
Another example of nested function
In this last example we are going to use the VLOOKUP() function to lookup a percentage for an amount. Please download the zipped file containing the workbook used for the example below.
The interesting part in this exercise is that there are 3 possibilities of percentages to multiply with the amount. The Scale column is used to determine if the percentage is drawn from the column 2, 3 or 4 of the Investment table above. So the logic goes like the following: Find the investment of Pedro in the first column of the Investment table. Then if the Scale is A use the percentage in column 2. If the Scale is B then use the percentage in column 3 otherwise use the percentage in column 4. The formula looks like this:
Follow each step closely and all will be fine. Once you’re done why not try it again!!
- In cell F11 type =VL when VLOOKUP is highlighted hit the Tab key and hit Ctrl+A to open the VLOOKUP() Function box.
- In the Lookup_value box click the Investment amount for Pedro. Cell D11. Hit Tab to move to the next box.
- In the Table_array box select the whole Investment table. Cells A2:D7. Hit F4 twice to lock the rows.
- In the Col_index_num this is where it gets interesting. The percentage comes from column 2, 3 or 4 depending on the Scale right.
So if the Scale is A then the percentage comes from column 2. If the Scale is B then the percentage comes from column 3 otherwise it will come from the column 4.
- In the Col_index_num type IF() and click the IF() in the Formula Bar. The Function box now become the IF() Function box.
- In the Logical_test type E11=”A” then hit the Tab key.
- In the Value_if_true box type the value 2 (remember if the Scale is A then the percentage comes from column 2). Hit the Tab key.
- In the Value_if_false type another IF() and click that IF() in the Formula bar.
- In the Logical_test type E11=”B” then hit the Tab key.
- In the Value_if_true box type the value 3. Hit the Tab key.
- In the Value_if_false type the value 4. If it is not a A or a B … then it’s a C.
- Click the VLOOKUP() in the Formula bar to return to the top level. Type the value 1 in the Range_lookup box.
- The Vlookup is finished. The only thing left is to multiply the percentage by the Investment.
- Position the insertion point between the = and the VLOOKUP in the Formula bar. Click cell D11 and hit the multiplication character.
Your formula is complete. Click OK then double-click the Fill Handle to copy your formula down.
It looks like a lot of steps but if you do it one more times you will see that it is pretty straight forward and it should be easier. Imagine that in this last exercise that there was 5 or 6 percentage columns or more! You would simply need to repeat the nested IF() a couple more times. Easy!
Hope that you appreciated this post. Happy nesting!
Daniel from ComboProjects