Excel is a great tool to use for calculating commissions. Many commission structures are stepped or more complex than saying “2% of sales”. To figure out commissions when you have a more complex type of commission structure, using Excel formulas is a great way to do it.
Download the Sample Excel File here to Play along
Let’s say we have this sales commission structure:
This is a stepped commission. All sales above 250,000 get a 5% commission. However under 250,000 gets less of a commission.
Now here is the list of salesmen with their sales. We will need to calculate their sales commissions based off of this.
There’s different ways to accomplish this but one way I like to use is Nested IF statements. We need to build a couple of IF statements within eachother to represent the commission table.
So let’s start off by doing an IF statement just for the first part, which is >250,000 is 5% commissions.
So we go to cell F5 and will enter in the formula =IF(E4>250000,0.05,0)
So this IF statement is first checking the criteria is E4 greater than 250,000. If it is, it is saying .05, which is 5%. If it is not, then it is saying 0. This zero we will replace with another IF statement, but for now, I did it this way to show how the IF statement works.
So for over 250k, this works fine. But let’s now dd in the 2nd IF statement to check for the range of 150k to 250k.
So we will add that part in and the new complete IF statement is :=IF(E4>250000,0.05,IF(E4>149999,0.03,0))
I hilighted above the new IF statement. If it checking if the sales are over 149,999. Since our range is from 150,000 to 249,999. We already checked if the sales are over 250,000, so we only need to check if they are over 149,999. If it is, then we use .03, which is 3%.
Now let’s finish up this IF statement with the last criteria. We need to check if it is over 74,999. The reason I use that instead of 75,000 is because the range says from 75,000 to 149,999.
So we will finish up this formula by making it this: =IF(E4>250000,0.05,IF(E4>149999,0.03,IF(E4>74999,0.01,0)))
We added the 3 criteria of over 74,999. The final criteria is the last part of that IF statement. Since all sales under 75,000 are 0 commissions, it is ok to leave that part as 0.
So now when I copy this down, we have:
This just shows what our commission percentages are. We want to know actual commissions. So we need to multiply it by the sales to get those commissions. So we need to add that. It is now:
And that’s how we get our commissions! Hope this was helpful to you. Please Like or Share if you found this helpful.