MAKING EXCEL WORK FOR YOU

Heading

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy


Nesting Excel Functions…

When you are working with EXCEL functions, it is often necessary to put one function inside another one. You are often capable of doing these functions without using the function wizard on the cell bar. However, when the nesting becomes complicated, it is often much easier to use this.

For example, to make a nested IF statement, just click the "=" sign and then choose the IF function. If you are going to have another IF statement for the "true" or "false" condition, simply fill in the other lines of the wizard and then go to the box. Instead of typing, click the "=" sign again and choose the IF function again. You can nest a large number of IF statements this way without worrying about all the parentheses being correct.

Nested IF functions are a simple way to do a complex conditional formula. Nested IF functions allow you to use up to seven IF functions to impose conditions, extended in Excel since 2007.

Essentially, the way a nested if statement would work is like this:

"if the first condition is true", then enter "this", otherwise if the second condition is true", then enter "this" otherwise enter "this"

It looks quite long and off-putting, but its really not too difficult once you understand the concept. A good example would be l a spreadsheet that you use to calculate discounts based on volume purchased. sales force.

From 50 to 99 units earns 10% discount

From 100 to 199 earns 15% discount

Anything over 200 earns 20% discount

Assuming the amount of items purchased is in column A, this is what the nested IF function would look like:

=IF(A2<50,0,IF(A2<=99,10,IF(A2<=199,15,IF(A2>=200,20))))

Perhaps you want to give some discount based on last years Sales. So you need to add a column that displays the Total Purchases for the previous period. Then by using AND, you can check this value. For example, this formula checks whether sales in the previous period exceeded 5000, if so it applies a 5% discount:

=IF((AND(B2>=5000,A2>1)),5,0)

To now to combine the discount criteria to ensure that customers who purchased over 5000 units in the previous period get a minimum discount of 5% the two formulas can be nested.

=IF((AND(B9>=5000,A9<50)),5,IF(A9<50,0,IF(A9<=99,10,IF(A9<=199,15,IF(A9>=200,20)))))

This looks extremely complicated but by comparing the previous formulas you can see that it simply checks the previous period's purchases and if they exceed 5000 apply 5% discount if not 0%. However, after that the previous discount rules apply.

This example demonstrates the use of multiple functions in one formula, i.e. "nesting". In this example we use the IF() function which can be nested up to seven instances. The second example shows how to create a SUM() function dependent on the contents of another cell.

Try amending the values in the relevant cells, note the formulas are in the shaded cells.    

Click to download an example workbook.