MrExcel.com - Your One Stop for Excel Tips & Solutions

Tiered Commission Plan with IF

March 22, 2022 - by

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.

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.

The VP of sales might announce a commission structure like this: Over $20K is 2%, over $15K is 1.25% and so on down to Over $1K is 0.1%.
Figure 388. 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.

Implementing that commission table with nested IF statements requires 5 nested IF functions.
Figure 389. 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.)

Instead, restate the commission table from lowest to largest. $0 sale is 0%. $1000 sale is 0.1%. $7500 sale is 0.25%. $10K sale is 1%. $15K sale is 1.25%. $20K sale is 2%. This table can then be used with VLOOKUP, MATCH, or XLOOKUP.
Figure 390. 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.

The bonus formula is now a simple VLOOKUP of the amount into the small-to-large table. Note this is the approximate match version of VLOOKUP.
Figure 391. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jeremy Thomas on Unsplash