# 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: