# Building Nested Functions Easy

# About Nested Functions

In all the years that I have been showing Excel Functions, there is a feature that consistently puzzles many users. 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:

=IF(AVERAGE(A2:A30)>0.7,"Great","Hummm!")

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():

=VLOOKUP($A15,$A2:$G10,MATCH(B14,$A1:$G1,0),0)

The VLOOKUP pros will quickly recognize this formula solving a *weakness* in VLOOKUP when it is copied across. But the question remains knowing the technique allowing to *easily* nest a function inside a function at any level without loosing your sanity? Yes there is!

### The technique involves the Function Arguments dialog box

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:

=IF(OR(A2="Melbourne","Adelaide"),B2*(1-E$2),B2)

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.

#### Summary

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 Arguments** box for a complex function you previously created you can use two simple ways.

- Click the cell containing the formula then click the
button to the left of the Formula Bar or press Shift+F3.*fx* - Click inside a nested level of your formula in the Formula Bar (in our example click somewhere in the OR level) then click the
button to the left of the Formula Bar or press Shift+F3. This will open the*fx***Function Arguments**box for the selected level!

### 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 as follow: 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:

=D11*VLOOKUP(D11,A$2:D$7,IF(E11="A",2,IF(E11="B",3,4)),1)

### Let’s Start

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.

## Conclusion

It looks like a lot of steps but if you do it a few more times you will see that it is pretty straight forward and will become 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