Calculate Commission Structure With Nested If Conditions

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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


***************************************************

Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.