Tiered commissions are quite common for people who do sales.
Tiered commissions provide an incentive for salespeople to boost their sales figures in order to earn more commission. This, in turn, can lead to more revenue for the company.
For example, there could be a tiered Commission structure where you pay 2% after sales value if you make sales of less than $100,000, 3% if sales are more than $100,000 but less than $500,000, and 4% if the sales are more than $500,000.
In this Excel tutorial, I will show you a couple of ways to quickly calculate tiered commissions in Excel (using simple formulas such as IF and VLOOKUP)
Calculate Tiered Commission Using Nested IF Formula
Below I have a data set where I have the tiered Commission structure in the table on the right (in blue color) and I have the sales data for some of the Sales Rep in the table on the left.
Note: All the formulas covered in this tutorial would require the Commission structure table to be in an ascending order, where we have the sales value from lowest to highest (as shown in column E) and their corresponding Commission percentage value in the adjacent cell.
With this dataset, I want to calculate the Commission that each salesperson would make based on their sales value that is in column B.
To do this, we would need to create a formula where the sales value of each salesperson is checked through the Commission structure table, and based on it a Commission percentage is returned. using this Commission percentage, we can calculate how much Commission should be paid out to the salesperson.
Below is the IF formula that would give us the commission value that the sales rep should get based on their sale value:
The above formula uses a nested if structure where an IF function is used within another IF function.
Let me quickly explain how it works.
When I use this formula, it goes through the Commission structure values in column E one by one.
It first checks whether the Commission value is less than 100,000 or not. If the sale value is less than 100,000, it would pick up the commission value from the adjacent column (which is 2%), and if the sales value is more than $100,000, it would move to the next cell.
The formula would continue to check the sale value in column E till it reaches a condition when the IF function condition is TRUE.
Based on the sale value, this nested IF formula would check the values in column E and return.
While this formula works great, if you have multiple tiers in the Commission structure, it could become long and complicated.
Calculate Tiered Commission Using IFS Formula
IFS is a new function that was released in excel 2019. as the name suggests, it allows you to use multiple if conditions within the same formula.
This addresses one of our concerns with the nested if statement – just that it could become long and complicated as multiple conditions are used.
Below I have the same data set where I need to calculate the Commission of sales Rep in column C based on the Commission tier structure table in blue color.
And here is the IFS formula that will do this for me:
Within the IFS function, you can use multiple conditions and also specify what value should be returned in case the condition is true.
Below is the syntax of the IFS function.
=IFS(Condition 1, Value if Condition 1 is True, Condition 2, Value to Return if Condition 2 is True….)
In our example, we started with the lowest tier of Commission and started checking whether the sales value is less than the lowest value in that ear or not.
So the first condition is to check whether the sale value is less than 100,000 or not, and if it is, then return 2%.
In case it’s not true, then it moves to the next condition where it checks whether the sales value is less than 200,000 or not, and so on.
While the IFS function may still look a little long and complicated, it’s easier than using multiple nested-IF statements. Personally, I find it easier to audit an IFS formula as compared to multiple-nested-IF formula.
Calculate Tiered Commission Using the VLOOKUP Formula
A lot of people shudder when they hear about the VLOOKUP formula. While there is no doubt that the VLOOKUP formula is amazing and can do wonders for you, a lot of Excel people still find it a bit complicated to use.
But in our situation, where we need to calculate commissions based on a tiered Commission table structure, using VLOOKUP would actually be easier than using a nested if formula or the IFS formula.
I don’t worry about not getting the VLOOKUP formula, I will explain exactly how it works and I guarantee you will get a hang of it once I’m done explaining it.
Below I have the data set where I need to calculate the sales Commission for each salesperson based on the tiered Commission table structure on the right.
Note that when you’re using the VLOOKUP formula method to calculate commission, you need to make one slight change in your tiered Commission table structure.
You need to make sure that the first value in your commission tier is 0, and the second value is the first threshold, which is 100,000 in our example. and the Commission in front of zero would be what a person would earn if the sale value is between 0 and 100,000 (which is 2% in this example).
And here’s the simple and easy VLOOKUP formula that will do this for us:
As you can see, this formula is a lot shorter and less complicated than the nested-IF and IFS formulas that I showed in the previous sections.
Now let me quickly explain how it works.
In the VLOOKUP formula, there are four arguments that we have used:
- The first one is the lookup value, which is B2 in this example. This is the sales value for which we want to find out the Commission value for the salesperson
- The second one is the look-up range, which is the table in which the formula would look for or look up value (which is B2). in this formula, we have used $E$2:$F$7 as the look-up range, where the formula would go through the left-most column to find out the lookup value, and then we can use the same range to fetch the commission value which is in the adjacent column (which is column F)
- The third argument is the column number from which we want to fetch the value that the formula would return. in this example, since we want the Commission percentage value, we have used 2 (which indicates that I want to get the value from the 2nd row in the range that I specified as the second argument, which is $E$2:$F$7)
- And finally, TRUE is used as the 4th argument which tells the formula that I’m not looking for an exact match, but rather an approximate match. with that when the formula is looking for the lookup value in cell B2, it would go through the Commission tier in column E and try to find out the closest match which is equal to or less than the lookup value. For example, when they look up value is 11732, which is less than 100,000, the formula would give us the commission value mentioned in front of 0 (as 0 is less than the next tier value which is higher than the lookup value). Similarly, if the lookup value is 271395, it would give us 4%, as that is the Commission percentage mentioned in front of 200,000 (because the next year is 300,000 which is bigger than or look up value)
So these are three different methods you can use to quickly calculate the commission based on a tiered Commission structure in Excel.
Personally, I would always go for the VLOOKUP method as it’s easier and less error-prone.
But in case you’re not comfortable using Vlookup, you can use the IFS function (if you’re using Excel 2019 or later versions), or the nested-IF formula (which works in all Excel versions).
I hope this article was useful and would help you quickly calculate the Commission based on the tiered-structure table you have. Also, remember that you need to have your Commission tier structure table sorted in ascending order for any of these formulas to work.
Other Excel articles you may also like:
- BETWEEN Formula in Excel (Using IF Function) – Examples
- Using IF Function with Dates in Excel (Easy Examples)
- Excel Logical Test Using Multiple If Statements in Excel [AND/OR]
- How to Compare Two Columns in Excel (using VLOOKUP & IF)
- Multiple If Statements in Excel (Nested Ifs, AND/OR) with Examples
- How to Calculate Cumulative Percentage in Excel? 3 Easy Ways!