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:
<tbody>
</tbody>
<tbody>
</tbody>
<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!!!
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!!!