Calculate Commission Structure With Nested If Conditions

Excel function 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


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