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 fx button to the left of the Formula Bar or press Shift+F3.
• Click in the Formula Bar inside a nested level of your formula then click the fx button or press Shift+F3 to open the 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 Investment column in the lookup table is 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