How to use excel nested functions

You’ve been given the task of assigning each of your company’s sales staff to one of three possible performance groups. In accord with how their sales numbers rank, you are to assign them to group A (Top salesperson), B (Knows his business) or C (Needs additional training). Because three results are possible, a simple IF function is insufficient to get to the desired outcome. Still, it is a straightforward matter to get the desired result by using excel nested IF functions.
In this learning module, you will be shown how to use excel nested functions. Excel nNested IF functions, often used in real-life situations, are described in detail.

Excel nested functions: HOW DOES IT WORK?


In the current example, the salespersons have already been ranked (column C). Now, in column D, you want to display an A, if the salesperson’s rank is 1 to 3, a B, if the rank is 4 to 6, or a C, if the person’s rank is 7 to 9.

You can readily carry out this assignment by nesting two IF functions, that is, by using a second IF function as a argument in the first IF function. The following diagram indicates how nesting two IF functions can lead to the desired result:

First select the cell that is to contain the result, in this case D4. Then start the Function Wizard by clicking the  button just to the left of the formula bar. In the Insert Function dialog box that opens, you will find the IF function in the Logical category. After selecting the IF function and clicking OK, the Function Arguments dialog box will appear (shown below). For the Logical_test argument, you can enter the condition C4<=3 into the Logical_test input box. This means that if the value in C4 is less than or equal to 3, the Value_if_true argument will be used. Therefore, for the ranks 1 to 3, enter an “A” into the Value_if_true input box. (If you don’t type the quotation marks, Excel will insert them for you.)

Click the Value_if_false input box. You are now going to insert a second IF function in the Value_if_false argument. Click the small black arrow next to the Name Box. A selection list will open from which you can select another function to insert into your existing function’s argument. In the list, select IF.

Excel nested functions: HINT

Whenever you nest one function in another function’s argument, you must either type in the excel nested function manually or insert it by choosing it from the list in the Name Box. Although intuitive, if you try to insert a function into the argument field of another function by clicking the button, the first function will be closed and dismissed.

Excel nested functions: HINT

If the function you want to insert in another function’s argument field is not among those shown in the names list, click More Functions…, the last entry in the list. You can then choose a function from all available functions.

After you’ve chosen the desired function (in this case, an IF function), another window will open in which you can enter arguments for the second function.

You can see the entire expression, as it develops, in the formula bar. The second function, the one you are currently editing, is highlighted in bold. Now enter the arguments for the second function. In our example, you should enter C4<=6 in the Logical_testinput box. If you now enter a B in the Value_if_true input box and a C in the Value_if_false input box, when you conclude the function by clicking OK, a plain text B will appear in D4 for rankings between 4 and 6 and a C will appear for rankings of 7 and above. Remember, for values less than or equal to 3, a plain text A will appear as a result of the initial IF function.

When you click OK, the entire process is concluded. You will not be returned to the first function.

If you click on D4, the function bar will display:

You can read this as follows: If the value in cell C4 is less than or equal to 3, automatically insert an A in the cell containing the function. Otherwise, check to see if the value in C4 is smaller than or equal to 6; if it is, insert a B, otherwise (for 7 or more) insert a C.

You can use AutoFill to copy the function to the other cells in column D.

Excel nested functions:CAUTION

To nest one function in another function, you have to select the second function via the Name Box. If you start the Function Wizard instead, the first function will be closed and dismissed. Alternatively, you can always type the second function into an argument field of the first function manually.

Excel nested functions: HINT

If you want to check to see whether you’ve entered all arguments as you intended, click the function in the formula bar. The structure of the function will be displayed below the formula bar. If you then click on the [Value_if_false] within it (as shown below), the function you entered in the Value_if_false argument of the first IF function will be highlighted in the formula bar (as shown).

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen