Complicated Formula? Maximums

[Lmaonade]

Hi,

I'm trying to create a tool to work out commissions using a certain set of rules and tiers. I've attached a screenshot of my spreadsheet.

So basically I've been having trouble trying to work out the formulas to put in these cells: S12, S13, S19, S20.

The way I want it to work is. The maximum in those cells can only be what's in the From and To cells next to them. so in cell S12 the maximum this can be is £37,000. BUT as there has already been £25,000 in the previous month, this can only be £12,000 maximum now. (Basically the maximum amount in the Billing column can only be whatever the number is in the W column). The numbers in L4, L5 and L6 give the information. So in Month 1. £25,000 was made. Tier 1 is 0 to 12,000 so 12,000 goes straight into here with the other 13,000 going into tier 2. Month 1 is easy for me to create formulas for. But now Month 2 has £33,000 more. so I need formulas to put £12,000 of this into S12 and the other £21,000 into S13. But I also need S13 to be set up to only take £25,000 maximum and anything else I will set to be put into Tier 4.

Then for Month 3, £50,000 was made. I would need to do the same but this time S18 (Tier1) would be 0 (As £12,000 is already put in Month 1), S19 (Tier 2) would also be 0 (As £13,000 is in Month 1 and £12,000 in month 2) and then S20 (Tier 3) would need to get £4,000 put into it from this £50,000 and the rest I'll put into Tier 4.

If anyone can help me with the formulas I would need to use for S12, S13, S19, S20 I'd be massively appreciative. This forum is always so helpful and I thank anyone that can help in advance. If you need more info please let me know.

Thanks,
Andy

 

[Dave Patton]

An image is not very helpful.
You can post an extract of your sheet with the forum's tool XL2BB.
You can calculate the commission on the cumulative sales see below.
You can paste the post into a clean sheet. Click on the icon below the f(x) and then move to your sheet and paste.
see YouTube magic trick 673

Commission2020.xlsm
	A	B	C	D	E	F
1	Bracket/Hurdle	Rate	Rate Differential
2	0	5%	5%
3	12,000	10%	5%
4	37,000	20%	10%
5	62,000	25%	5%
6
7		Jan	Feb	Mar	Apr	May
8	Input amounts: Sales	25,000.00	33,000.00	30,000.00
9
10	Commission	1,900.00	5,400.00	7,300.00	0.00	0.00
11
12	Total	88,000.00	14,600.00
3a

Cell Formulas
Range	Formula
C2:C5	C2	=B2-N(B1)
B10:F10	B10	=SUMPRODUCT(--(SUM($B$8:B8)>rB),SUM($B$8:B8)-rB,rDiff)-SUM($A$10:A10)
B12	B12	=SUM(B8:H8)
C12	C12	=SUMPRODUCT(--(B12>rB),B12-rB,rDiff)

Named Ranges
Name	Refers To	Cells
'3a'!rB	='3a'!$A$2:$A$5	C12, B10:F10
'3a'!rDiff	='3a'!$C$2:$C$5	C12, B10:F10

 

[Dave Patton]

I added the arithmetic D2:D5 and the total calculation now shows the values for the brackets and rate differentials.

Commission2020.xlsm
	A	B	C	D	E
1	Bracket/Hurdle	Rate	Rate Differential	Arithmetic
2	0	5%	5%	600.00
3	12,000	10%	5%	2,500.00
4	37,000	20%	10%	5,000.00
5	62,000	25%	5%	6,500.00
6				14,600.00
7		Jan	Feb	Mar	Apr
8	Input amounts: Sales	25,000.00	33,000.00	30,000.00
9
10	Commission	1,900.00	5,400.00	7,300.00	0.00
11
12	Total	88,000.00	14,600.00
3a

Cell Formulas
Range	Formula
C2:C5	C2	=B2-N(B1)
D2:D5	D2	=MAX(0,MIN($B$12,A3)-A2)*B2
D6	D6	=SUM(D2:D5)
B10:E10	B10	=SUMPRODUCT(--(SUM($B$8:B8)>rB),SUM($B$8:B8)-rB,rDiff)-SUM($A$10:A10)
B12	B12	=SUM(B8:H8)
C12	C12	=SUMPRODUCT(--(B12>{0;12000;37000;62000}),B12-{0;12000;37000;62000},{0.05;0.05;0.1;0.05})

Named Ranges
Name	Refers To	Cells
'3a'!rB	='3a'!$A$2:$A$5	B10:E10, D2
'3a'!rDiff	='3a'!$C$2:$C$5	B10:E10

 

[Dave Patton]

You can complete the calculations without the Table of Brackets and Rates.
I named the array information for Brackets and Rate Differential in Name Manager.
aB = {0;12000;37000;62000} and aR ={0.05;0.05;0.1;0.05}

Commission2020.xlsm
	K	L	M	N
1
2				%
3	Month 1	25,000.00	1,900.00	7.60%
4	Month 2	33,000.00	5,400.00	16.36%
5	Month 3	50,000.00	12,300.00	24.60%
6	Quarter	108,000.00	19,600.00	18.15%
3aa

Cell Formulas
Range	Formula
M3:M5	M3	=SUMPRODUCT(--(SUM($L$3:L3)>aB),SUM($L$3:L3)-aB,aR)-SUM($M$2:M2)
N3:N6	N3	=M3/L3
L6:M6	L6	=SUM(L3:L5)