This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Tiered Commission Plan with IF
Problem: I am calculating a commission based on a sliding scale. The rate is based on the size of the sale, using the table shown here.
Strategy: You can solve this with several IF statements or with the unusual form of the VLOOKUP function.
Figure 393 Sales above $15K and less than $20K are paid at 1.25%.
To use the IF function, it is important that you start looking for the largest category first. Say that a cell contains a sale of $21,000. Checking for F2>20000 would return a TRUE, but checking for F2>1000 would be TRUE as well. You need to start checking for the largest value. If the sale is not larger than that value, then move on to checking for smaller values.
In the formula below, the IF function is finding the correct rate. The result of the IF function is multiplied by the revenue in F2. This prevents you from having to enter *F2 five different times in the formula.
Figure 394 Five IF statements nested together.
The formula is =IF(F2>20000,0.02,IF(F2>15000,0.0125,IF(F2>10000,0.01,IF(F2>7500,0.0025,IF(F2>1000,0.001,0)))))*F2.
As the commission plan becomes more complex, you would have to keep adding more IF statements. The current limit is 32 IF statements nested together. As recently as Excel 2003, the limit was 7 IF statements. It does not take long before this method becomes unwieldy.
You’ll be learning more about VLOOKUP after about 15 more topics. Most VLOOKUP formulas in this book end with a FALSE to indicate a close match. Here is one case where a VLOOKUP that omits the FALSE can save the day.
To use a VLOOKUP, you have to reverse the order so that the largest lookup value appears at the end of the table. Add a beginning row with zero to handle the sales smaller than $1000. (Actually, depending on how you handle negative values, the negative values might need to be first.)
Additional Details: You might some day have a situation where you need Excel to find the value in the table that is just larger. You can not do this with VLOOKUP, but you can do it with MATCH. The last argument in MATCH can be 0 for exact match, 1 for the value just lower or -1 for the value just higher. Combine MATCH with INDEX to replicate a range-lookup where you want the just-higher value.