Sliding scale commission formula

[airnitta]

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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Salesperson 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nett Commission Income[/TD]
[TD]Consultant Split[/TD]
[/TR]
[TR]
[TD]Tier 1: $0 to $100,000[/TD]
[TD]45%[/TD]
[/TR]
[TR]
[TD]Tier 2: >$100,000 to $200,000[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]Tier 3: >$200,000 to $250,000[/TD]
[TD]55%[/TD]
[/TR]
[TR]
[TD]Tier 4: >$250,000 to $300,000[/TD]
[TD]60%[/TD]
[/TR]
[TR]
[TD]Tier 5: >$300,000[/TD]
[TD]65%[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Salesperson 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nett Commission Income[/TD]
[TD]Consultant Split[/TD]
[/TR]
[TR]
[TD]Tier 1: $0 to $50,000[/TD]
[TD]45%[/TD]
[/TR]
[TR]
[TD]Tier 2: >$50,000 to $100,000[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]Tier 3: >$100,000 to $150,000[/TD]
[TD]55%[/TD]
[/TR]
[TR]
[TD]Tier 4: >$150,000 to $200,000[/TD]
[TD]60%[/TD]
[/TR]
[TR]
[TD]Tier 5: >$200,000 to $250,000[/TD]
[TD]65%[/TD]
[/TR]
[TR]
[TD]Tier 6: >$250,000[/TD]
[TD]70%[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Salesperson 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nett Commission Income[/TD]
[TD]Consultant Split[/TD]
[/TR]
[TR]
[TD]Tier 1: $130,000 to $200,000[/TD]
[TD]45%[/TD]
[/TR]
[TR]
[TD]Tier 2: >$200,000[/TD]
[TD]50%[/TD]
[/TR]
</tbody>[/TABLE]



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!!!

 

[Michael M]

Assuming data in A1, use

=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

 

[AhoyNC]

See if this link helps.

http://www.mcgimpsey.com/excel/variablerate.html

 

[airnitta]

Assuming data in A1, use

=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?

 

[Michael M]

Hmm, a different question altogether really....
Have a look at this thread for how to..

https://www.mrexcel.com/forum/excel-questions/992540-cumulative-tier-commission-formula-excel.html

 

[alpadem]

Hi Sir,
You could use this table, and generate for others,
[TABLE="width: 500"]
<tbody>[TR]
[TD]Down (A)[/TD]
[TD]Top (B)[/TD]
[TD]Rate (C)[/TD]
[TD]Rate Difference (D)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD]45%
[/TD]
[TD]45%[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]100.000[/TD]
[TD]50%[/TD]
[TD]%5[/TD]
[/TR]
[TR]
[TD]100.001[/TD]
[TD]200.000[/TD]
[TD]55%[/TD]
[TD]%5[/TD]
[/TR]
[TR]
[TD]200.001[/TD]
[TD]250.000[/TD]
[TD]60%[/TD]
[TD]%5[/TD]
[/TR]
[TR]
[TD]250.001[/TD]
[TD]300.000[/TD]
[TD]65%[/TD]
[TD]%5[/TD]
[/TR]
[TR]
[TD]300.001[/TD]
[TD]UP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Income[/TD]
[TD]E1[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Commission[/TD]
[TD]SUMPRODUCT((E1>B2:B6)*(E1-B2:B6)*(D2:D6))[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

Excel 2010
	B	C	D	E
1	199,000.00	94,500.00
2
3		0.00	45%	45%
4		100,000.00	50%	5%
5		200,000.00	55%	5%
6		250,000.00	60%	5%
7		300,000.00	65%	5%
8
2d

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(--(B1>C3:C7),B1-C3:C7,E3:E7)
E3	=D3-N(D2)