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.
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.
- Sales above $15K and less than $20K are paid at 1.25%.
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.
- 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.)
-
Lookup table where the values go from smallest to largest.
In the table above, a sale of $5000 is not found in the table. Using a typical VLOOKUP with FALSE at the end would result in an #N/A error. When you leave off the FALSE, Excel will look for the value that is just smaller than 5000. In this case, it will return the 0.10% since 1000 is the level just smaller than $5000.
-
Leave off FALSE. Lookup finds the just-smaller value.
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.