The IF() function in Excel allows you to evaluate a situation which has two possible outcomes (e.g. sales are greater than $1000) and calculate a different value for each outcome. However, sometimes you need to work with situations where there are more than two possible outcomes. That's where multiple, or nested, IF functions come in handy. In this tutorial we'll cover how to use nested IF functions to calculate sales commission for a team of sales people, given a range of different commission rates.

This lesson assumes you are already familiar with the IF function. If you aren't, you can learn how to use IF statements here.

Remember that the IF function works by evaluating a logical test - a calculation which can only be TRUE or FALSE, or 1 or 0. It then includes a calculation to perform if the logical test is TRUE, and another calculation if it is FALSE.

Nested IF functions work by replacing one or both of the TRUE/FALSE calculations with another IF function.

Calculating commission for a sales team based on monthly sales

Imagine you have a sales team of five people, and you need to calculate their commission for the month based on their sales figures.

• You put the data into the following table:
  
• Your commission plan works as follows:
  • If someone sells less than $400 in a month, they get 7% commission.
  • For sales between $400 and $750, they get 10% commission.
  • For sales between $750 and $1000, they get 12.5%
  • For sales over $1000, they get 16%
• Rather than calculate each of these commission figures individually, you decide to use a nested IF formula instead. The logical tests you would use in this case are these:
  • Is commission less than $400? If TRUE, then calculate commission.
  • If FALSE, then is commission less than $750? If TRUE then calculate commission.
  • If FALSE, then is commission less than $1000? If TRUE then calculate commission.
  • If FALSE, then calculate commission (because it must be more than $1000 - we don't need to do another logical test for this).
• The formula to represent this to calculate commission for Bob looks like this (I've highlighted the logical tests in bold to help you understand now the formula is put together:
  =IF(B4<400,B4*7%,IF(B4<750,B4*10%,IF(B4<1000,B4*12.5%,B4*16%)))
• This formula can be confusing the first time you look at it. Let me run through it again.
  • The logical test in the first IF statement checks if the sales figure in B4 is less than $400. If it is, it calculates commission at 7% and stops calculating. Otherwise, it must be greater than or equal to $400, so we move on to the next IF statement.
  • The logical test in the second IF statement checks if the sales figure in B4 is less than $750. We already know it must be more than $400 or we wouldn't have got to this point. If it is less than $750, it calculates commission at 10%. Otherwise it must be greater than or equal to $750, so we move on to the next IF statement.
  • The logical test in the third and final IF statement checks if the sales figure in B4 is less than $1000. If it is, it calculates commission at 12.5%. Otherwise, it must be greater than or equal to $1000, so it calculates commission at 16%. At this point there are no more IF statements, no more logical tests we need to do, and we have our answer.
• Here's how our spreadsheet example looks once the formula has been entered into the Commission column. I've put the formula for each sales person in the cell next to the commission calculation so you can see how it looks:
  
• Check Rajiv's commission figure in the table above. Rajiv sold $750 in the month, which is right on the threshold between 10% and 12.5%. What commission does he get? He actually gets 12.5% because the formula checks whether his sales were less than $750, not less than or equal to $750. That means he receives commission at 12.5%. So it's important to pay attention to how you construct your logical tests.

Some things to remember when using nested IF functions

Nested IF functions are very powerful, but there are some things to consider before you dive in and start using them.

• As you can see from the commission formula above, using nested IF statements gets complicated quite quickly. Trying to decipher this takes a moment or two, especially if you haven't looked at the spreadsheet in a while.
• Making changes is also challenging - imagine if you decided to introduce another commission threshold of $850. You'd need to add another IF function into the formula.
• You can have up to 64 IF functions nested in a formula in Excel 2007, 2010 and Excel 2013. Excel 2003 only supported 7 IF functions in one formula. That said, if I find myself needing more than 10 I start considering other approaches to my problem - managing that many IF functions in one formula quickly gets complicated, never mind if you have to manage 20, 30, 40 or more.
• The VLOOKUP function can sometimes be a better solution in a scenario like this. You can see a really good example of where you should learn how the VLOOKUP function works in one of the comments below.
• That's not to say that VLOOKUP is automatically a better solution:
  • A commission spreadsheet like the one shown above is a good example where using VLOOKUP would be a better approach.
  • However, if you needed to use different calculations depending on the outcome of each logical test, then nested IF statements may well be a a better way to go.
  • An example might be where sales people who exceed $1000 in sales also get a bonus of $100. The formula would then look like the following, which is not so easy (although not impossible) to do with a VLOOKUP function:
    =IF(B4<400,B4*7%,IF(B4<750,B4*10%,IF(B4<1000,B4*12.5%,B4*16%+100)))

Finally, I've supplied a copy of the spreadsheet I used for the example above. You can download it here.