Need help Please setting up Cumulative Commission Structure Need Formula from excel gurus

Hey guys Need a formula set up to do a commission table as structured below.


Incremental % Tier
2% $250.00
5% $500.00
9% $750.00
12% $1,000.00

Trying to set up a formula where if sales were to reach $250 the commission would be $5. Anything below $250 would be zero commission.

If sales were $350 then that would only be 2% total commission.

If sales were to reach above $500 lets say $550 however it would unlock the 5% tier and commission in this case would be ($250*2%)+($300*5%) = $17.5.

If $800 was reached it would unlock 9% tier (being above $750) and would be ($250*2%)+($250*5%)+($300*9%)= $44.5

Been at this for a bit and could really use some help. Thanks!

Excel 2016 (Windows) 64 bit

	A	B	C	D	E
1	Incremental%	Tier		Sales	Comm
2	0%	$0.00		500	17.5
3	2%	$250.00
4	5%	$500.00
5	9%	$750.00
6	12%	$1,000.00
7

Sheet: Sheet1

E2

Formula:

Please Login or Register  to view this content.

D2 having the sales value suppose it is $500 then copy the above formula and paste it into E2 simply

May be I am wrong, but First 250 would be 2 %, next in hand sales would be 350, now again 250 would be 2% and rest 100 would be 5%. Am I wrong?

	A	B	C	D
1	Sales	Comm Rate	Delta
2	$0	0%	0%	C2: =B2 - N(B1)
3	$250	2%	2%
4	$500	5%	3%
5	$750	8%	3%
6	$1,000	11%	3%
7
8	Sales	Commission
9	$0	$0.00		B9: =SUMPRODUCT((A9 > $A$2:$A$6) * (A9 - $A$2:$A$6) * $C$2:$C$6)
10	$125	$0.00
11	$250	$0.00
12	$300	$1.00
13	$500	$5.00
14	$700	$15.00
15	$750	$17.50
16	$900	$29.50
17	$1,000	$37.50
18	$2,000	$147.50