Tier calculation

[ferrigeu]

small problem, i think...
below is a table in columns A-D, starting at row 2
if the customer has assets between 0-1'000'000.00, the cost of service is 5%
if the customer has assets of 12'000'000.00 then the cost of service is
5% for the first 1'000'000.00,
3% for 1'000'000.01 - 10'000'000, and
2% for the balance of 2'000'000.00 in the tier of 10'000'000.01 to 12'000'000.00

i cant use arrays or VBA as we use a rather restrictive IT environment
thank you for your help

	Min ($)	Max ($)	Cost per annum
Tier 1	-	1,000,000	5.00%
Tier 2	1,000,000.01	10,000,000	3.00%
Tier 3	10,000,000.01	1,000,000,000	2.00%

 

[ferrigeu]

the asset data is in K7-K37
the results are in L7-L37

EOD Holdings ($)	Result
5,868
17,603
58,675
117,350
117,350
176,025
469,401
762,777
1,349,529
1,936,280
4,870,038
4,870,038
4,870,038
5,456,790
6,923,669
8,390,548
11,324,306
11,382,981
11,441,656
12,028,408
12,321,784
12,908,535
13,495,287
15,255,542
15,255,542
15,196,866
14,962,166
14,962,166
14,668,790
14,668,790

 

[Dave Patton]

N.B.
The names are not necessary; it is a personal preference.
The names can be assigned to be applicable to the sheet or to the workbook.
Determine the numbers by looking at the tier specifications or by reviewing the ranges.
For example with the formula in B2, select B7:B9-B6:B8 and press F9.

The formula in C2 uses named arrays. The array of Brackets data and the array of Rate Differentials are named.
See Formulas Name Manger
- The array of Bracket data is named aB Refers to ={0;1000000;10000000}
- The array of Rate Differentials is named aR Refers to ={0.05;-0.02;-0.01}
Advantages
The formula is easier to read.
The table is not required if the formula C2 is used.

Commission2022.xlsm
	A	B	C	D
1	Total sales	10,000,000.00
2	Commission	320,000.00	320,000.00
3
4
5	Brackets	Rates	--- Arithmetic ---
6	cell b7 is blank		By Bracket	Cumulative
7	0	5%	50,000.00	50,000.00
8	1,000,000	3%	270,000.00	320,000.00
9	10,000,000	2%	0.00	320,000.00
10	1E+308
1aa

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>A7:A9),B1-A7:A9,B7:B9-B6:B8)
C2	C2	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
C7:C9	C7	=MAX(0,MIN($B$1,A8)-A7)*B7
D7	D7	=MAX(0,MIN($B$1,A8)-A7)*B7+N(D6)
D8:D9	D8	=MAX(0,MIN($B$1,A9)-A8)*B8+D7
A10	A10	=BigNum

Commission2022.xlsm
	K	L
7	5,868	293.40
8	14,668,790	413,375.80
9
1aa

Cell Formulas
Range	Formula
L7:L8	L7	=SUMPRODUCT(--(K7>aB),K7-aB,aR)