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_test*input 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).

Kein Banner zum Anzeigen