Calculate cumulative commissions with tiers

[vdam]

Commissions with thresholds
I have to calculate commission taking into account statistical amount for cycle. Please help find formula in excel?

• Amount 1: 1000.00 EUR,
• Amount 2: 1500.00 EUR.

There are rules set up defining that we should apply:

• A1 - for amount <= 500.00 (0%)
• B89 - for amount from 500.01 to 700.00 (2%)
• B66 - for amount >700.00 (1%, min 5.00eur)

For Amount 1 following commissions was calculated: A1 on 500 (0.00 EUR), B89 on 200 (4.00 EUR) , B66 on 300 (5.00 EUR).

For Amount 2 B66 on 2500 was calculated, because of accumulated amount. Amount already was >700, so B66 was used.

 

[Dave Patton]

T202009b.xlsm
	A	B	C	D
1		Amount	Amount	Total
2		1,000.00	1,500.00	2,500.00
3	Cumulative	1,000.00	2,500.00
4	Commission	7.00	15.00	22.00
5	Minimum	2.00	0.00	2.00
6	Total	9.00	15.00	24.00
1f

Cell Formulas
Range	Formula
D2,D4:D6	D2	=SUM(B2:C2)
B3:C3	B3	=SUM($A2:B$2)
B4:C4	B4	=SUMPRODUCT(--(B3>{500;700}),B3-{500;700},{0.02;-0.01})-SUM($A4:A4)
B5:C5	B5	=IF(B3>700,MAX(0,9-N(A5)-B4),0)
B6:C6	B6	=B4+B5

 

[CA_Punit]

Deleted