Page 9 / 13

Nested functions

Excel functions are powerful in themselves, but their real power comes when we can combine nested functions.

In practice, nested functions have one function as an argument to another.

We practice this in two different exercises.

Video

The objective

Next, we will work on tasks in the Product groups sheet and in the Pay increases sheet.

Product groups

The Product groups sheet contains product groups defined by their code (number). If the code is 1, it is “Product group 1” and if the code is 2, it is “Product group 2”. Otherwise, the product group is unknown.

The product group is identified using two nested IF functions. Example:

Pay increases

In the Pay increases sheet, we will build a slightly more complex calculation to work out the pay increases for the company's employees. The calculation uses the IF and MAX functions, which are nested.

We calculate a pay rise and a new salary for each employee. The salary will be increased by 3.5%, but not less than $65.50. On the other hand, only certain employees are entitled to a pay rise.

Task: Make a formula that recognizes product groups

Go to the Product groups sheet in the exercise book.

Set the following conditions for the function:

#### Condition 1	If the product group code is 1, the product group description is Product group 1.
#### Condition 2	If the product group code is 2, the product group description is Product group 2.
#### Otherwise	The product group description is Unknown product group.

You can write the formula by hand or use the function library. Most people who use functions a lot find it easier and more straightforward to write it by hand.

Nice

Writing nested functions manually

Start writing nested functions from the outer function. When typing its arguments, if you are at the point where you want to write an inner function, Excel may provide function options for the formula when you type its name. Write the inner function to its end and then continue writing the outer one. It is important to make sure that all the necessary arguments and parentheses for each function are included in the formula.

User starts typing =IF(B2=1; 'Product group 1'; and Excel offers options for functions starting with J

In this formula, the condition is that if the product group number is 1, then the product group description is Product group 1. But since we want to add another condition, it is written in the place where the argument Value_if_False would normally be. This way, the formula can be continued, and the last argument in the last formula will be the Value_if_False argument.

=IF(Logical_test;Value_if_True;IF(Logical_test;Value_if_True;Value_if_False))

Note the number of parentheses at the end of the formula.

Now the formula includes a nested IF function

Adding nested functions via the function library

Start adding the first function as normal.

When you're at the point where you're adding another function, click the box on the left of the scroll bar and select the new function.

In the function insertion window, a new function is selected in the Value_if_False box from the upper left of the window

A second formula window opens, where you can add the necessary arguments.

Insert the arguments for the inner function in the new window

Task: Calculate pay rise on the basis of percentage increase

Do the task in the Pay increases spreadsheet.

Let's first build the formula in cell D5, from where we copy it to the following cells.

Let's start building the formula with a simple calculation of the salary increase.

Increase=Salary*Percentage increase

Example:

Task: Take the minimum increase into account

Take the minimum increase into account in the calculation of the raise. The pay increase should thus be at least the one indicated in cell B2.

Example:

Nice

We calculate this using the **MAX** function. The wage increase is therefore either the calculation we made in the previous section or the value of cell **B2**.

Copy the formula again to all cells.

Task: Take into account whether the employee is entitled to a raise

Finally, we take into account whether the employee is entitled to a raise. In our table, this is indicated by the word “Yes” appearing in column B. We implement the constraint with the IF function.

Example:

	#### The formula When writing the formula, make sure you put commas and parentheses in the right places.
	IF eligible for an increase is “Yes”
	THEN the pay rise is in line with our previous formula
	ELSE the pay rise is 0