# Sliding scale commission formula

#### airnitta

##### New Member
Hi all,
I am not an advanced user of Excel by any means and am struggling to create a formula for calculating sliding scale sales commissions.
I'm the manager of a real estate office and require a formula to assist with automatically calculating the commission payable to an agent based on their sale figures below:

 Salesperson 1 Nett Commission Income Consultant Split Tier 1: \$0 to \$100,000 45% Tier 2: >\$100,000 to \$200,000 50% Tier 3: >\$200,000 to \$250,000 55% Tier 4: >\$250,000 to \$300,000 60% Tier 5: >\$300,000 65%

<tbody>
</tbody>

 Salesperson 2 Nett Commission Income Consultant Split Tier 1: \$0 to \$50,000 45% Tier 2: >\$50,000 to \$100,000 50% Tier 3: >\$100,000 to \$150,000 55% Tier 4: >\$150,000 to \$200,000 60% Tier 5: >\$200,000 to \$250,000 65% Tier 6: >\$250,000 70%

<tbody>
</tbody>

 Salesperson 3 Nett Commission Income Consultant Split Tier 1: \$130,000 to \$200,000 45% Tier 2: >\$200,000 50%

<tbody>
</tbody>

Would like this to be a single cell solution, so need 3 formulas in total - 1 for each salesperson
Any assistance would be greatly appreciated!!!

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Michael M

##### Well-known Member
Assuming data in A1, use
Code:
``=A1*LOOKUP(A1,{0,100000,200000,250000,300000,1000000},{0.45,0.5,0.55,0.6,0.65})``
You can use the same principle to the other salespeople

#### airnitta

##### New Member
Assuming data in A1, use
Code:
``=A1*LOOKUP(A1,{0,100000,200000,250000,300000,1000000},{0.45,0.5,0.55,0.6,0.65})``
You can use the same principle to the other salespeople

Thanks for your quick reply - I forgot to mention that the sliding scale is accumulative, so if the agent has sold enough properties for a total of \$199,000 commission, then the break down would be:
\$45,000 (first \$100k) and then \$49,500 (\$100k-\$199k) for a total split of \$94,500 -

I think the formula above just picks wherever the total amount lies and finds the closest split - is there a way of calculating like I have described above?

##### New Member
Hi Sir,
You could use this table, and generate for others,
 Down (A) Top (B) Rate (C) Rate Difference (D) 0 45% 45% 0 100.000 50% %5 100.001 200.000 55% %5 200.001 250.000 60% %5 250.001 300.000 65% %5 300.001 UP

<tbody>
</tbody>

 Income E1

<tbody>
</tbody>
 Commission SUMPRODUCT((E1>B2:B6)*(E1-B2:B6)*(D2:D6))

<tbody>
</tbody>

Replies
6
Views
153
Replies
2
Views
112
Replies
3
Views
460
Replies
3
Views
750
Replies
9
Views
612

1,130,186
Messages
5,640,688
Members
417,161
Latest member
Devon150

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back