Formula for tiered commission on different quantity/price levels

[Mak1]

Hi hope this makes sense.....

I need to work out commission on (1) quantity of product and (2) its sale price.

I would appreciate some suggestions and help on the 2 points below:

• The commission is 50% for any sales of products equal to or above £5 value and decreases by 0.1% for every penny less than £5.
  The formula I have for this so far :
  =((0.5-(5-C19)*0.1)*C19) where C19 is the sales price.
  
  Can this be simplified or tidied up? Also how do I include it allow a sales price over £5 to stick at 50% commission?
  
  
• The above formula is for the first 5000 units. Thereafter every 1000 units sold the commission amount (result of formula from point above) is decreased/discounted by 1%.
  
  What would the formula be, if the number of units sold is E19 and I wanted to show the total commission in F19?
  
  For e.g.
  A) If 7000 units were sold at £6.00, commission would be as follows:
  1) 50% for the first 5000 units ( £3 x 5000 ) = £3000
  2) (50% for the units 5001 to 6000 ; £3 x 1000) with a 1% discount = £2970
  3) (50% for the units 6001 to 7000 ; £3 x 1000) with a 2% discount = £2940
  TOTAL £8910
  
  B) If 7000 units were sold at £0.60, commission would be as follows:
  1) 6% for the first 5000 units ( £0.036 x 5000 ) = £180
  2) (6% for the units 5001 to 6000 ; £0.036 x 1000) with a 1% discount = £35.64
  3) (6% for the units 6001 to 7000 ; £0.036 x 1000) with a 2% discount = £35.28
  TOTAL £250.92
  
  
  Should you have any questions please let me know.

 

[Mak1]

What are the brackets and rates if you base the calculations on the value of the of the sales?

With that information, what are your expected results?

Hi Dave,
"What are the brackets and rates if you base the calculations on the value of the of the sales?"

The calculation is based on two variables:

1) sales price:
Payout = 50% for any sales of products equal to or above £5 value and decreases by 0.1% for every penny less than £5.

2) amount:
The above payout (in point 1) is for the first 5000 units. Thereafter every 1000 units sold the payout is decreased/discounted by 1%.

"With that information, what are your expected results?"

One line breakdown with automated calculations, when the two variables above are the only inputs required. i.e. a sale of x units at £y per unit. I assume this will have to refer to a previous sheet with the tier breakdowns.


Using two examples; first a sale of 7001 units at £6.00, and secondly a sale of 7001 units at £0.60:

The table below is sorted as below:

Columns A to E : Kirk's route
Columns G to N: My route ( worked out from internet research )
Columns Q to S: Dave's route

Rows 1 to 23: 3 x routes' above calculations for payout amount based on the two sales examples

Rows 25 to 30 show a one line simplification. This section is a crude layout of what I expect to be able to populate when I need to input a sale of x units at £y per unit. So for example usiny my route:

Ideally the formula in K29 : "=SUMPRODUCT(--(I29>$H$3:$H$6),--(I29-$H$3:$H$6),$K$3:$K$6) " should somehow reference that the "$K$3:$K$6" range is variable depending on J29. Is there any we could populate I29 and J29 (this layout would be on Sheet2) to use as variables in N4,N3, which subsequently changes K3:K6 and calculates the amount in N5 (this layout would be on Sheet1) and result of N5 bought forward into K29? My experience with Excel is very basic and not sure if MATCH, LOOKUP, SUMPRODUCT or VBA route will be the best thing to use here, but having a worksheet breakdown of each product line is not desired, just one excel file with minimum sheets, whereby I can list 1 product line or 30 product lines based on 2 inputs (amounts and sales price) per line, and the commission due is calculated.

20.03.22 trial commission sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	KIRK's £6 Route							My £6 Route									Dave's £6 Route
2		Units Sold	Sales Price	Tiers	Commission			Tier Min	Tier Max	Payout	Dif Rate						Amount	7001	20913.21
3		7001	6	5000	15000			-	5,000	£ 3.00	3.0000000000		Sales Price	£ 6.00
4				1000	2970		1	5,001	6,000	£ 2.970	(0.0300000000)		Amount	7001			Brackets	Payout
5				1000	2940		2	6,001	7,000	£ 2.940	(0.0300000000)		Payout	£ 20,913.00
6				1	2.91		3	7,001	8,000	£ 2.910	(0.0300000000)						0	3
7																	5000	2.97
8																	6000	2.9403
9																	7000	2.9109
10																	8000	2.88179
11																	9000	2.85297
12
13	KIRK's £0.60 Route							My £0.60 Route									Dave's £0.60 Route
14		Units Sold	Sales Price	Tiers	Commission / Payout			Tier Min	Tier Max	Payout	Dif Rate						Amount	7001	250.96
15		7001	0.6	5000	180			-	5,000	£ 0.03600	0.0360000000		Sales Price	£ 0.60
16				1000	35.64		1	5,001	6,000	£ 0.03564	(0.0003600000)		Amount	7001.00			Brackets	Payout
17				1000	35.28		2	6,001	7,000	£ 0.03528	(0.0003600000)		Payout	£ 250.96
18				1	0.03492		3	7,001	8,000	£ 0.03492	(0.0003600000)						0	0.036
19																	5000	0.03564
20																	6000	0.03528
21																	7000	0.03493
22																	8000	0.03458
23																	9000	0.03424
24
25	Kirk's Example simplified into one line per transaction						My Example simplified into one line per transaction										Dave's simplified into one line per transaction
26
27	Product	Amount sold	Sales price	Commission / Payout	Supplier Revenue			Product	Amount sold	Sales price	Commission / Payout	Supplier Revenue					Product	Amount sold	Sales price	Commission / Payout	Supplier Revenue
28
29	1	7001	£ 6.00	20912.91	£ 21,093.09			1	7001	£ 6.00	20913.00	£ 21,093.00					1	7001	£ 6.00	20913.21	£ 21,092.79
30	2	7001	£ 0.60	250.95	£ 3,949.65			2	7001	£ 0.60	250.96	£ 3,949.64					2	7001	£ 0.60	250.96	£ 3,949.64
Sheet1

Cell Formulas
Range	Formula
S2,S14	S2	=SUMPRODUCT(--(R2>Q6:Q11),R2-Q6:Q11,R6:R11-R5:R10)
D3	D3	=MIN(5000,$B$3)
E3,E15	E3	=((0.5-MAX(0,0.001*100*(5-C3)))*C3)*D3
D4:D9	D4	=IF(SUM(D$3:D3)=$B$3,"",MIN(1000,$B$3-SUM(D$3:D3)))
E4:E9,E19:E21	E4	=IF(ISNUMBER(D4),$E$3/$D$3*D4*(1-ROWS(E$3:E3)/100),"")
J3	J3	=IF($N$3>5,$N$3*0.5,((0.5-(5-$N$3)*0.1)*$N$3))
K3,K15	K3	=J3
J4:J6	J4	=$J$3*((100-G4)*0.01)
K4:K6,K16:K18	K4	=J4-J3
N5	N5	=SUMPRODUCT(--(N4>$H$3:$H$6),--(N4-$H$3:$H$6),$K$3:$K$6)
R7:R11,R19:R23	R7	=R6*0.99
D15	D15	=MIN(5000,$B$15)
D16:D21	D16	=IF(SUM(D$15:D15)=$B$15,"",MIN(1000,$B$15-SUM(D$15:D15)))
E16:E18	E16	=IF(ISNUMBER(D16),$E$15/$D$15*D16*(1-ROWS(E$15:E15)/100),"")
J15	J15	=IF($N$15>5,$N$15*0.5,((0.5-(5-$N$15)*0.1)*$N$15))
J16:J18	J16	=$J$15*((100-G16)*0.01)
N17	N17	=SUMPRODUCT(--(N16>$H$15:$H$18),--(N16-$H$15:$H$18),$K$15:$K$18)
D29	D29	=SUMPRODUCT(E3:E6)
E29:E30,U29:U30,L29:L30	E29	=(C29*B29)-D29
D30	D30	=SUMPRODUCT(E15:E18)
K29	K29	=SUMPRODUCT(--(I29>$H$3:$H$6),--(I29-$H$3:$H$6),$K$3:$K$6)
K30	K30	=SUMPRODUCT(--(I30>$H$15:$H$18),--(I30-$H$15:$H$18),$K$15:$K$18)
T29	T29	=SUMPRODUCT(--(R29>Q6:Q11),R29-Q6:Q11,R6:R11-R5:R10)
T30	T30	=SUMPRODUCT(--(R30>Q18:Q23),R30-Q18:Q23,R18:R23-R17:R22)

I've just noticed your recent reply, and as I input 7001 units at £0.60 the payout is off and I think this is not taking into account the decreases by 0.1% for every penny less than £5 as per table below:

Book1
	A	B	C	D	E
1
2
3				Brackets	Payout
4
5				0	0.5
6				5000	0.495
7				6000	0.49005
8				7000	0.48515
9				8000	0.480298
10				9000	0.475495
11
12		Number	Price	Amount
13		7001	6	29892.12
14		108	10.8	583.2
15		432	8.64	1866.24
16		7001	0.6	2989.212
Sheet1

Cell Formulas
Range	Formula
E6:E10	E6	=E5*0.99
D13:D16	D13	=SUM(IF(B13>ab,(B13-ab)*ar))*C13

Named Ranges
Name	Refers To	Cells
ab	=Sheet1!$D$5:$D$10	D13:D16
ar	=Sheet1!$E$5:$E$10	D13:D16, E6

 

[Mak1]

Hi Dave,
"What are the brackets and rates if you base the calculations on the value of the of the sales?"

The calculation is based on two variables:

1) sales price:
Payout = 50% for any sales of products equal to or above £5 value and decreases by 0.1% for every penny less than £5.

2) amount:
The above payout (in point 1) is for the first 5000 units. Thereafter every 1000 units sold the payout is decreased/discounted by 1%.

"With that information, what are your expected results?"

One line breakdown with automated calculations, when the two variables above are the only inputs required. i.e. a sale of x units at £y per unit. I assume this will have to refer to a previous sheet with the tier breakdowns.


Using two examples; first a sale of 7001 units at £6.00, and secondly a sale of 7001 units at £0.60:

The table below is sorted as below:

Columns A to E : Kirk's route
Columns G to N: My route ( worked out from internet research )
Columns Q to S: Dave's route

Rows 1 to 23: 3 x routes' above calculations for payout amount based on the two sales examples

Rows 25 to 30 show a one line simplification. This section is a crude layout of what I expect to be able to populate when I need to input a sale of x units at £y per unit. So for example usiny my route:

Ideally the formula in K29 : "=SUMPRODUCT(--(I29>$H$3:$H$6),--(I29-$H$3:$H$6),$K$3:$K$6) " should somehow reference that the "$K$3:$K$6" range is variable depending on J29. Is there any we could populate I29 and J29 (this layout would be on Sheet2) to use as variables in N4,N3, which subsequently changes K3:K6 and calculates the amount in N5 (this layout would be on Sheet1) and result of N5 bought forward into K29? My experience with Excel is very basic and not sure if MATCH, LOOKUP, SUMPRODUCT or VBA route will be the best thing to use here, but having a worksheet breakdown of each product line is not desired, just one excel file with minimum sheets, whereby I can list 1 product line or 30 product lines based on 2 inputs (amounts and sales price) per line, and the commission due is calculated.

20.03.22 trial commission sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	KIRK's £6 Route							My £6 Route									Dave's £6 Route
2		Units Sold	Sales Price	Tiers	Commission			Tier Min	Tier Max	Payout	Dif Rate						Amount	7001	20913.21
3		7001	6	5000	15000			-	5,000	£ 3.00	3.0000000000		Sales Price	£ 6.00
4				1000	2970		1	5,001	6,000	£ 2.970	(0.0300000000)		Amount	7001			Brackets	Payout
5				1000	2940		2	6,001	7,000	£ 2.940	(0.0300000000)		Payout	£ 20,913.00
6				1	2.91		3	7,001	8,000	£ 2.910	(0.0300000000)						0	3
7																	5000	2.97
8																	6000	2.9403
9																	7000	2.9109
10																	8000	2.88179
11																	9000	2.85297
12
13	KIRK's £0.60 Route							My £0.60 Route									Dave's £0.60 Route
14		Units Sold	Sales Price	Tiers	Commission / Payout			Tier Min	Tier Max	Payout	Dif Rate						Amount	7001	250.96
15		7001	0.6	5000	180			-	5,000	£ 0.03600	0.0360000000		Sales Price	£ 0.60
16				1000	35.64		1	5,001	6,000	£ 0.03564	(0.0003600000)		Amount	7001.00			Brackets	Payout
17				1000	35.28		2	6,001	7,000	£ 0.03528	(0.0003600000)		Payout	£ 250.96
18				1	0.03492		3	7,001	8,000	£ 0.03492	(0.0003600000)						0	0.036
19																	5000	0.03564
20																	6000	0.03528
21																	7000	0.03493
22																	8000	0.03458
23																	9000	0.03424
24
25	Kirk's Example simplified into one line per transaction						My Example simplified into one line per transaction										Dave's simplified into one line per transaction
26
27	Product	Amount sold	Sales price	Commission / Payout	Supplier Revenue			Product	Amount sold	Sales price	Commission / Payout	Supplier Revenue					Product	Amount sold	Sales price	Commission / Payout	Supplier Revenue
28
29	1	7001	£ 6.00	20912.91	£ 21,093.09			1	7001	£ 6.00	20913.00	£ 21,093.00					1	7001	£ 6.00	20913.21	£ 21,092.79
30	2	7001	£ 0.60	250.95	£ 3,949.65			2	7001	£ 0.60	250.96	£ 3,949.64					2	7001	£ 0.60	250.96	£ 3,949.64
Sheet1

Cell Formulas
Range	Formula
S2,S14	S2	=SUMPRODUCT(--(R2>Q6:Q11),R2-Q6:Q11,R6:R11-R5:R10)
D3	D3	=MIN(5000,$B$3)
E3,E15	E3	=((0.5-MAX(0,0.001*100*(5-C3)))*C3)*D3
D4:D9	D4	=IF(SUM(D$3:D3)=$B$3,"",MIN(1000,$B$3-SUM(D$3:D3)))
E4:E9,E19:E21	E4	=IF(ISNUMBER(D4),$E$3/$D$3*D4*(1-ROWS(E$3:E3)/100),"")
J3	J3	=IF($N$3>5,$N$3*0.5,((0.5-(5-$N$3)*0.1)*$N$3))
K3,K15	K3	=J3
J4:J6	J4	=$J$3*((100-G4)*0.01)
K4:K6,K16:K18	K4	=J4-J3
N5	N5	=SUMPRODUCT(--(N4>$H$3:$H$6),--(N4-$H$3:$H$6),$K$3:$K$6)
R7:R11,R19:R23	R7	=R6*0.99
D15	D15	=MIN(5000,$B$15)
D16:D21	D16	=IF(SUM(D$15:D15)=$B$15,"",MIN(1000,$B$15-SUM(D$15:D15)))
E16:E18	E16	=IF(ISNUMBER(D16),$E$15/$D$15*D16*(1-ROWS(E$15:E15)/100),"")
J15	J15	=IF($N$15>5,$N$15*0.5,((0.5-(5-$N$15)*0.1)*$N$15))
J16:J18	J16	=$J$15*((100-G16)*0.01)
N17	N17	=SUMPRODUCT(--(N16>$H$15:$H$18),--(N16-$H$15:$H$18),$K$15:$K$18)
D29	D29	=SUMPRODUCT(E3:E6)
E29:E30,U29:U30,L29:L30	E29	=(C29*B29)-D29
D30	D30	=SUMPRODUCT(E15:E18)
K29	K29	=SUMPRODUCT(--(I29>$H$3:$H$6),--(I29-$H$3:$H$6),$K$3:$K$6)
K30	K30	=SUMPRODUCT(--(I30>$H$15:$H$18),--(I30-$H$15:$H$18),$K$15:$K$18)
T29	T29	=SUMPRODUCT(--(R29>Q6:Q11),R29-Q6:Q11,R6:R11-R5:R10)
T30	T30	=SUMPRODUCT(--(R30>Q18:Q23),R30-Q18:Q23,R18:R23-R17:R22)

I've just noticed your recent reply, and as I input 7001 units at £0.60 the payout is off and I think this is not taking into account the decreases by 0.1% for every penny less than £5 as per table below:

Book1
	A	B	C	D	E
1
2
3				Brackets	Payout
4
5				0	0.5
6				5000	0.495
7				6000	0.49005
8				7000	0.48515
9				8000	0.480298
10				9000	0.475495
11
12		Number	Price	Amount
13		7001	6	29892.12
14		108	10.8	583.2
15		432	8.64	1866.24
16		7001	0.6	2989.212
Sheet1

Cell Formulas
Range	Formula
E6:E10	E6	=E5*0.99
D13:D16	D13	=SUM(IF(B13>ab,(B13-ab)*ar))*C13

Named Ranges
Name	Refers To	Cells
ab	=Sheet1!$D$5:$D$10	D13:D16
ar	=Sheet1!$E$5:$E$10	D13:D16, E6

Not sure of this helps but this is the example of the possible layouts (rows 32 to 48) that I could adapt for reference sheet 1, but am looking for a way that does not need a table with references for every single possible price from £0.10 to £5+? Hence my thoughts in previous message "Ideally the formula in K29 : "=SUMPRODUCT(--(I29>$H$3:$H$6),--(I29-$H$3:$H$6),$K$3:$K$6) " should somehow reference that the "$K$3:$K$6" range is variable depending on J29....."

20.03.22 trial commission sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
25	Kirk's Example simplified into one line per transaction						My Example simplified into one line per transaction										Dave's simplified into one line per transaction
26
27	Product	Amount sold	Sales price	Commission / Payout	Supplier Revenue			Product	Amount sold	Sales price	Commission / Payout	Supplier Revenue					Product	Amount sold	Sales price	Commission / Payout	Supplier Revenue
28
29	1	7001	£ 6.00	20912.91	£ 21,093.09			1	7001	£ 6.00	20913.00	£ 21,093.00					1	7001	£ 6.00	20913.21	£ 21,092.79
30	2	7001	£ 0.60	250.95	£ 3,949.65			2	7001	£ 0.60	250.96	£ 3,949.64					2	7001	£ 0.60	250.96	£ 3,949.64
31
32	REFERENCE SHEET 1 (v1) for looking up and applying price to volume formula?																REFERENCE SHEET 1 (v2) ?
33
34	*FOREX USED																			£0.60 rates		£6 rates
35	GBP	EUR	USD															Tier Min	Tier Max	Payout	Dif Rate	Payout	Dif Rate
36	£ 1.00	€ 1.20	$ 1.36															-	5,000	£ 0.03600	0.0360000000	£ 3.00	3.0000000000
37					GBP sales price examples used in tables below, showing consequent EUR and USD conversions at current FOREX rates.												1	5,001	6,000	£ 0.03564	(0.0003600000)	£ 2.970	(0.0300000000)
38																	2	6,001	7,000	£ 0.03528	(0.0003600000)	£ 2.940	(0.0300000000)
39	Sales price per unit				Split%			Supplier amount per unit				Commission amount per unit					3	7,001	8,000	£ 0.03492	(0.0003600000)	£ 2.910	(0.0300000000)
40	GBP	EUR	USD		Supplier	Us		GBP	EUR	USD		GBP	EUR	USD
41
42	£ 5.00	€ 6.00	$ 6.80		50.0%	50.0%		£ 2.50000	€ 3.00	$ 3.40		£ 2.50000	£ 3.00000	$ 3.40000
43	£ 4.95	€ 5.94	$ 6.73		50.5%	49.5%		£ 2.49975	€ 3.00	$ 3.40		£ 2.45025	£ 2.94030	$ 3.33234
44	£ 4.90	€ 5.88	$ 6.66		51.0%	49.0%		£ 2.49900	€ 3.00	$ 3.40		£ 2.40100	£ 2.88120	$ 3.26536
45	£ 4.85	€ 5.82	$ 6.60		51.5%	48.5%		£ 2.49775	€ 3.00	$ 3.40		£ 2.35225	£ 2.82270	$ 3.19906
46	£ 4.80	€ 5.76	$ 6.53		52.0%	48.0%		£ 2.49600	€ 3.00	$ 3.39		£ 2.30400	£ 2.76480	$ 3.13344
47	£ 4.75	€ 5.70	$ 6.46		52.5%	47.5%		£ 2.49375	€ 2.99	$ 3.39		£ 2.25625	£ 2.70750	$ 3.06850
48	£ 4.70	€ 5.64	$ 6.39		53.0%	47.0%		£ 2.49100	€ 2.99	$ 3.39		£ 2.20900	£ 2.65080	$ 3.00424
Sheet1

Cell Formulas
Range	Formula
D29	D29	=SUMPRODUCT(E3:E6)
E29:E30,U29:U30,L29:L30	E29	=(C29*B29)-D29
D30	D30	=SUMPRODUCT(E15:E18)
K29	K29	=SUMPRODUCT(--(I29>$H$3:$H$6),--(I29-$H$3:$H$6),$K$3:$K$6)
K30	K30	=SUMPRODUCT(--(I30>$H$15:$H$18),--(I30-$H$15:$H$18),$K$15:$K$18)
T29	T29	=SUMPRODUCT(--(R29>Q6:Q11),R29-Q6:Q11,R6:R11-R5:R10)
T30	T30	=SUMPRODUCT(--(R30>Q18:Q23),R30-Q18:Q23,R18:R23-R17:R22)
T36	T36	=IF($N$15>5,$N$15*0.5,((0.5-(5-$N$15)*0.1)*$N$15))
U36,W36	U36	=T36
V36	V36	=IF($N$3>5,$N$3*0.5,((0.5-(5-$N$3)*0.1)*$N$3))
T37:T39	T37	=$T$36*((100-Q37)*0.01)
U37:U39,W37:W39	U37	=T37-T36
V37:V39	V37	=$V$36*((100-Q37)*0.01)
B42:B48	B42	=A42*$B$36
C42:C48	C42	=A42*$C$36
H42:H48	H42	=A42*E42
I42:I48	I42	=B42*E42
J42:J48	J42	=C42*E42
L42:L48	L42	=A42*F42
M42:M48	M42	=B42*F42
N42:N48	N42	=C42*F42

 

[KRice]

Give this version a test drive. This is a single line formula (no helper tables or lookups needed) that makes use of some of Excel 365's new functions to create two arrays. One array holds the sales quantities in each tier. The other array holds the per unit commission for each tier according to the declining percentage method described. Then SUMPRODUCT operates on those two arrays. Please let me know if you encounter any errors with it.

MrExcel20220319.xlsx
	A	B	C	D
1		Units Sold	Sales Price	Total Commission
2		7001	£6.00	£20,912.91
3		12305	£4.00	£19,200.96
4		2500	£11.40	£14,250.00
5		1001	£6.00	£3,003.00
6		5	£3.00	£4.50
7		9500	£10.00	£46,875.00
8		7000	£0.60	£250.92
9		7000	£6.00	£20,910.00
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=LET(tiers,1+MAX(0,CEILING($B2-5000,1000))/1000, seq,SEQUENCE(tiers,,1,1), SUMPRODUCT(SEQUENCE(tiers,,1,-0.01)*$C2*(0.5-MAX(0,0.001*100*(5-$C2))), IF(seq=1, MIN(5000,$B2), IF(seq<tiers, 1000, IF(seq=tiers, 1000*(MOD($B2,1000)=0)+$B2-FLOOR($B2,1000) ))) ))

 

[Mak1]

Give this version a test drive. This is a single line formula (no helper tables or lookups needed) that makes use of some of Excel 365's new functions to create two arrays. One array holds the sales quantities in each tier. The other array holds the per unit commission for each tier according to the declining percentage method described. Then SUMPRODUCT operates on those two arrays. Please let me know if you encounter any errors with it.

MrExcel20220319.xlsx
	A	B	C	D
1		Units Sold	Sales Price	Total Commission
2		7001	£6.00	£20,912.91
3		12305	£4.00	£19,200.96
4		2500	£11.40	£14,250.00
5		1001	£6.00	£3,003.00
6		5	£3.00	£4.50
7		9500	£10.00	£46,875.00
8		7000	£0.60	£250.92
9		7000	£6.00	£20,910.00
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=LET(tiers,1+MAX(0,CEILING($B2-5000,1000))/1000, seq,SEQUENCE(tiers,,1,1), SUMPRODUCT(SEQUENCE(tiers,,1,-0.01)*$C2*(0.5-MAX(0,0.001*100*(5-$C2))), IF(seq=1, MIN(5000,$B2), IF(seq<tiers, 1000, IF(seq=tiers, 1000*(MOD($B2,1000)=0)+$B2-FLOOR($B2,1000) ))) ))

WOW!

That is brilliant!

Thank you so much kind sir!

I'll give it a good test run and expect to mark this thread as solved

 

[Mak1]

WOW!

That is brilliant!

Thank you so much kind sir!

I'll give it a good test run and expect to mark this thread as solved

Big tick and gold star for you Kirk!

This is solved, super smashing great!

Thank you!!!!

 

[KRice]

You're welcome...we're happy to help.

 

[Dave Patton]

You can name the rate differential information,
Does the following give the results that you require for the 2 rate structures?

Commission2022.xlsm
	A	B	C	D
21		Units Sold	Sales Price	Total Commission
22		7,001	6	20,913.21
23		12,305	4	28,905.64
24		2,500	11.4	14,250.00
25		1,001	6	3,003.00
26		5	3	9.00
27		9,500	10	46,882.49
28		7,000	0.6	2,509.24
29		7,000	6	20,910.30
30		108	10.8	583.20
31		432	8.64	1,866.24
32		1,000	4.5	2,700.00
33		864	0.6	311.04
34		1,080	1.8	1,166.40
35
1ff

Cell Formulas
Range	Formula
D22:D34	D22	=SUM(IF(B22>aB,(B22-aB)*IF(C22>=5,aR,a6RR)))*C22

 

[Mak1]

You're welcome...we're happy to help.

Hi Kirk,

I've just spotted that the formula "as is" allows the commission to go into negative if units sold amounts got really high, as per table below and was wondering if there was any we could have a bottom level of commission rate at 0.01% or similar?

Commission Template Final 20.03.22.xlsx
	C	D	E
2	Units Sold	Sales Price	Total Commission
3	300000	6	-409800
4	180000	6	78000
5	200000	6	26700
Sheet1

Cell Formulas
Range	Formula
E3:E5	E3	=LET(tiers,1+MAX(0,CEILING($C3-5000,1000))/1000, seq,SEQUENCE(tiers,,1,1), SUMPRODUCT(SEQUENCE(tiers,,1,-0.01)*$D3*(0.5-MAX(0,0.001*100*(5-$D3))), IF(seq=1, MIN(5000,$C3), IF(seq<tiers, 1000, IF(seq=tiers, 1000*(MOD($C3,1000)=0)+$C3-FLOOR($C3,1000) ))) ))

 

[Mak1]

You can name the rate differential information,
Does the following give the results that you require for the 2 rate structures?

Commission2022.xlsm
	A	B	C	D
21		Units Sold	Sales Price	Total Commission
22		7,001	6	20,913.21
23		12,305	4	28,905.64
24		2,500	11.4	14,250.00
25		1,001	6	3,003.00
26		5	3	9.00
27		9,500	10	46,882.49
28		7,000	0.6	2,509.24
29		7,000	6	20,910.30
30		108	10.8	583.20
31		432	8.64	1,866.24
32		1,000	4.5	2,700.00
33		864	0.6	311.04
34		1,080	1.8	1,166.40
35
1ff

Cell Formulas
Range	Formula
D22:D34	D22	=SUM(IF(B22>aB,(B22-aB)*IF(C22>=5,aR,a6RR)))*C22

Hi Dave,

There seems to be some discrepancy here. The correct amounts for the first 8 lines values you posted are below:

Commission Template Final 20.03.22.xlsx
	C	D	E
2	Units Sold	Sales Price	Total Commission
3	7001	6	20912.91
4	12305	4	19200.96
5	2500	11.4	14250
6	1001	6	3003
7	5	3	4.5
8	9500	10	46875
9	7000	0.6	250.92
10	7000	6	20910
Sheet1

Cell Formulas
Range	Formula
E3:E10	E3	=LET(tiers,1+MAX(0,CEILING($C3-5000,1000))/1000, seq,SEQUENCE(tiers,,1,1), SUMPRODUCT(SEQUENCE(tiers,,1,-0.01)*$D3*(0.5-MAX(0,0.001*100*(5-$D3))), IF(seq=1, MIN(5000,$C3), IF(seq<tiers, 1000, IF(seq=tiers, 1000*(MOD($C3,1000)=0)+$C3-FLOOR($C3,1000) ))) ))

 

[KRice]

I see that. The issue is with the rule to decrease the commission rate by 1 % for each higher tier. This part of the formula creates that array of commission rate factors: SEQUENCE(tiers,,1,-0.01)
So when the number of tiers is less than 100, the array will be {1;0.99;0.98;...} and never going below 0, but in your example with a large number of units sold (300000 units), we'll have 296 tiers and the sequence goes negative. To set a lower bound, I think it would make sense to generate this array with an IF function. To try it out, I changed the name of the original sequence to seqt (for the sequence related to the tier count, and then created a new variable named seqr to generate the original sequence for commission rates...and then seqr is fed into an IF statement to change commission rates to 0.01 where they dropped below 0.01. This is the formula to try that out, were B10 holds the quantity sold:
=LET(tiers,1+MAX(0,CEILING($B10-5000,1000))/1000,seqt,SEQUENCE(tiers,,1,1),seqr,SEQUENCE(tiers,,1,-0.01),IF(seqr<0.01,0.01,seqr))
Making this change, revising the references to seqt and also tweaking the IF(seqt=tiers statement to incorporate an IF function rather than the somewhat more convoluted original version, the revised formula looks like this:

MrExcel20220319.xlsx
	A	B	C	D
1		Units Sold	Sales Price	Total Commission
2		7001	6	20912.91
3		12305	4	19200.96
4		2500	11.4	14250
5		1001	6	3003
6		5	1	0.5
7		9500	10	46875
8		7000	0.6	250.92
9		7000	6	20910
10		300000	6	169380
Sheet2

Cell Formulas
Range	Formula
D2:D10	D2	=LET(tiers,1+MAX(0,CEILING($B2-5000,1000))/1000, seqt,SEQUENCE(tiers,,1,1),seqr,SEQUENCE(tiers,,1,-0.01), SUMPRODUCT(IF(seqr<0.01,0.01,seqr)*$C2*(0.5-MAX(0,0.1*(5-$C2))), IF(seqt=1,MIN(5000,$B2), IF(seqt<tiers,1000, IF(seqt=tiers,IF(MOD($B2,1000)=0,1000,$B2-FLOOR($B2,1000)) ))) ))

Please let me know if this yields expected values and if you encounter any other issues.