Today I received a question from a reader who had purchased our Excel ebook, it’s a question that comes back fairly frequently so I thought I’d give a public answer. I’ve done similar examples but it’s always helpful to do it another time. The problem is fairly easy:
****************************
I am trying to figure out how to calculate a commission structure based on a range of dollar amts.
Say my gross profit is $10000
My range looks like this:
1-2500 5%
2501-5000 10%
5001-25000 15%
25000+ 20%
I’m guessing I can do this with IF function, but I can’t seem to nail down the syntax right.
****************************
There are a hundred different ways to do this but here is what I will do. I’ll start the opposite way that you would typically expect. First, I want to know the commission on sales of over $25,000. Basically, the vendor would earn:
5% on $2500 = $125
10% on $2500 ($5000-$2500) = $250
15% on $20,000 ($25,000-$5000) = $3000
So the vendor would earn $125+$250+$3000+20% (amount – $25,000) or $3375+ 20%
if the sales is in cell A1, then I’d get:
=IF(A1>25000,3375+0.2*(A1-25000),0)
Makes sense? That was one of the 4 scenarios. Now, I’ll move to a vendor that would sell between $5001 and $25000. He would generate a commission of:
5% on $2500 = $125
10% on $2500 ($5000-$2500) = $250
So the vendor would earn $125+$250+15% (amount – $5,000) or $375+ 15%
=IF(A1>5000,375+0.15*(A1-5000),0)
If I combine both together, I get:
=IF(A1>5000,375+0.15*(A1-5000),IF(A1>25000,3375+0.2*(A1-25000),0))
Now, I’ll move to a vendor that would sell between $2501 and $5000. He would generate a commission of:
5% on $2500 = $125
So the vendor would earn $125+10% (amount – $2500)
=IF(A1>2500,125+0.1*(A1-2500),0)
If I combine everything together, I get:
=IF(A1>5000,375+0.15*(A1-5000),IF(A1>25000,3375+0.2*(A1-25000),IF(A1>2500,125+0.1*(A1-2500),0)))
Then, I can simply replace the 0 (which is for cases where the sales are less than $2500) by 0.05*sales
=IF(A1>5000,375+0.15*(A1-5000),IF(A1>25000,3375+0.2*(A1-25000),IF(A1>2500,125+0.1*(A1-2500),A1*0.05)))
I dragged the formula and got a few examples:
You can also download the spreadsheet here
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
