# Building Nested Functions Easy

# About Nested Functions

This post has been thoroughly edited on May 1, 2020

In all the years that I have been teaching Excel Functions, a feature that has consistently puzzles many users is nested function. More precisely, the creation of *nested* functions. The problem is not about the concept behind nested functions, but about the *technique* of creating them.

### What are nested function again?

A nested function is a function that *returns* its result to another function one level up. Nesting functions are not rare at all! Typically I see functions having three or four levels of nesting but I have heard of 10+ levels!

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; the IF() function. Suppose that the average score was 82%, then the IF() function would evaluate this way:

=IF(0.82>0.7,"Great","Hummm!") The function would return:Great.

### Is it that hard?

I occasionally hear of participants that have handcrafted 6 levels of IF() functions … or more! But it took them more than 30 minutes to do so (seasoned with some frustration). The technique discussed in this post will reduce that time by 60% at least with very little efforts once we get the gist of it.

Below is another classic example using VLOOKUP():

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

VLOOKUP pros will quickly recognize this formula because it solves a *weakness* in VLOOKUP when it is copied *across.* But the question remains: Is there an *easy* way to nest as many functions as required without loosing your sanity? Yes there is! Lets see how to build such formula.

### The technique involves the Function Arguments dialog box

Let’s examine a simple example of formula that uses a nested function. In a new worksheet type the table below (or at least the first 2 or 3 rows).

##### Objectives

If the Sales in column B occurs in Melbourne **or** Adelaide then the **Discount** percentage in cell E2 is applied to the **Total** in column C otherwise the **Total** is the same as the **Price**. The Discount percentage is a constant applied to all transactions, so we will use the reference E$2.

The formula in C2 is shown below. Note the use of an **OR()** within a **IF()**.

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

Follow the steps below:

- In cell C2 type
**=IF**and press**Tab**to quickly add the opening parenthesis. Press Ctrl+A to open the IF() Function box. - Because the
**Logical_test**is one of two possible cities, type**OR()**in that box. - The
**Formula Bar**shows:**=IF(OR())**. In the**Formula Bar**click anywhere inside the function**OR()**. Excel displays the**OR()**Function box. Click inside**IF()**to move the focus back to the IF Function Arguments. Click back in the**OR()**and move in the**Logical1**in the Formula Arguments. - In the
**Logical1**click cell A2 then type =”Melbourne”. The expression is now A2=”Melbourne”. Tab to the second box click A5 again but this time type =”Adelaide” (don’t forget the double quotes!).

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 the focus to the**IF()**function. Click in the**Value_if_true**box. - While in the TRUE argument box click cell B2 and type *(1-E$2). The complete expression is:
**B2*(1-E$2)**. Hit Tab to move to the third box. - In the FALSE argument box simply click cell B5. The Function Arguments should look as the one below.

Click **OK** then double-click the Fill Handle to copy your formula down to the other cities. If you examine the Totals, you will note that Melbourne and Adelaide are slightly smaller than the Price. They are exactly 25% lower. If you change the percentage in cell E2 the two amounts will update.

#### Summary

As we have seen in this first example, to nest a function inside another function simply type the name of the *top* level function (hit **Tab** then press Ctrl+A) to bring up the **Function Arguments** for that function. Click in the first argument then type the level 2 function with the parentheses . 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 function you previously created (multi level or not) you can use two simple ways.

- Click the cell containing the formula then click the
button to the left of the*fx***Formula Bar**or press Shift+F3. - Click in the Formula Bar inside a nested level of your formula then click the
button or press Shift+F3 to open the*fx***Function Arguments**box*for that selected level*!

### Another example of nested function

In this last example we are going to use Excel to lookup a percentage of an **Investment** to pay; the **Dividend.** 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 by the **Investment.** The **Scale** column is used to determine which percentage is used from the **Investment** table above. The logic goes as follow: Lookup Pedro Kavana’s investment in the lookup table’s first column (the **Investment** column).

If the **Scale** is A use the percentage in column 2 **(Risk).** If the **Scale** is B then use the percentage in column 3 **(Growth)** otherwise use the one 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. There seems to be lots of steps because I broke the whole project in *small* individual moves. In reality it’s, five or six steps. Once you’re done, why not try it again!!

- In cell F11 type =VL when VLOOKUP is appears hit Tab and press Ctrl+A to open the VLOOKUP()
**Function Argument**box. - In the
**Lookup_value**box click the**Investment**amount for Pedro: Cell D11. Hit**Tab.** - In the
**Table_array**box select the whole**Investment**table: Cells A2:D7. Hit F4 twice to lock the rows. - The
**Col_index_num**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 Arguments**now shows the**IF()**arguments. - In the
**Logical_test**click cell E11 then type =”A”. The expression is**E11=”A”**. Hit Tab. - 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. - In the
**Value_if_false**type another**IF()**and click that**IF()**in the Formula bar. - In the
**Logical_test**click cell E11 then type =”B”. The expression is**E11=”B”**. Hit Tab. - In the
**Value_if_true**box type the value 3. Hit the Tab. - In the
**Value_if_false**type the value 4. If it is not 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 customer’s
**Investment.** - Position the insertion point between the = and the VLOOKUP in the
**Formula Bar**. Click cell D11 and hit the multiplication character. The formula now starts like this: =**D11***VLOOKUP(D11 …

Important. It is critical that the

Investmentcolumn in thelookup tableis sorted in ascending order. We want to find the bracket in which our investment falls in. That is why the last argument for VLookup is 1.

Your formula is complete. Click** OK** then double-click the Fill Handle to copy your formula down.

Last Tip

## 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 the exercise above 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