Increasing Scale for Commission

[Margin King]

Hi there.
Can someone help me with a consistent formula to calculate commission based on an increasing scale eg commission on revenue is 6% for the first £30k, 8% for the next £10k and 10% from then on.

Ideally the same formula in all the monthly cells highlighted in yellow in the attached image without using loads of 'Sum' & 'If's

Thanks

 

[arthurbr]

The technique is described at McGimpsey & Associates : Excel : How differential rates work

 

[Joe4]

Here is one way, for a total value in cell M3:

=(M3*0.06)+(MAX(M3-30000,0)*0.02)+(MAX(M3-40000,0)*0.02)

 

[Dave Patton]

An image is not very useful. The forum has a tool named XL2BB that you can use to post an extract of your information to the forum.


The first example includes a solution with Lambda. If you want to try this formula, please advise.
The second post uses Sum since you have Excel 365. With earlier versions, use SumProduct.

Commissions 2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								Commission Cumulative Revenue
2		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
3	Revenue	2,365.00	5,263.00	4,528.00	4,269.00	4,998.00	5,623.00	5,412.00	4,859.00	4,562.00	4,528.00	4,127.00	5,878.00	56,412.00
4
6	Commission	141.90	315.78	271.68	256.14	299.88	337.38	373.88	388.72	402.54	452.80	412.70	587.80	4,241.20
7
2f

Cell Formulas
Range	Formula
N3	N3	=SUM(B3:M3)
B6:M6	B6	=CommissionX(SUM($B$3:B3))-SUM($A$6:A6)
N6	N6	=CommissionX(N3)

Commissions 2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								Commission Cumulative Revenue
2		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
3	Revenue	2,365.00	5,263.00	4,528.00	4,269.00	4,998.00	5,623.00	5,412.00	4,859.00	4,562.00	4,528.00	4,127.00	5,878.00	56,412.00
4
5	Commission	141.90	315.78	271.68	256.14	299.88	337.38	373.88	388.72	402.54	452.80	412.70	587.80	4,241.20
6	Commission	141.90	315.78	271.68	256.14	299.88	337.38	373.88	388.72	402.54	452.80	412.70	587.80	4,241.20
7
8
9
10			Brackets	Rates	Rate Differential
11
12			0	6%	6%
13			30,000	8%	2%
14			40,000	10%	2%
2f

Cell Formulas
Range	Formula
N3	N3	=SUM(B3:M3)
B5:M5	B5	=SUMPRODUCT(--(SUM($B$3:B3)>$C$12:$C$14),SUM($B$3:B3)-$C$12:$C$14,$E$12:$E$14)-SUM($A$5:A5)
N5	N5	=SUM((N3>C12:C14)*(N3-C12:C14)*E12:E14)
B6:M6	B6	=CommissionX(SUM($B$3:B3))-SUM($A$6:A6)
N6	N6	=CommissionX(N3)
E12:E14	E12	=D12-D11

 

[Dave Patton]

Commissions 2023.xlsm
	A	B	C											N
1
2		Jan	Feb											Total
3	Revenue	2,365.00	5,263.00											56,412.00
4
5	Commission	141.90	315.78											4,241.20
6	Commission	141.90	315.78											4,241.20
7
2f

Cell Formulas
Range	Formula
N3	N3	=SUM(B3:M3)
B5:C5	B5	=SUM((SUM($B$3:B3)>aB)*(SUM($B$3:B3)-aB)*aR)-SUM($A$5:A5)
N5	N5	=SUM((N3>aB)*(N3-aB)*aR)
B6:C6	B6	=CommissionX(SUM($B$3:B3))-SUM($A$6:A6)
N6	N6	=CommissionX(N3)

The bracket and rate differential array information is shown in previous post.

With Name Manager, create a new name CommissionX or a name that is relevant.

Value =LAMBDA(CumRev,LET(b,{0;30000;40000},r,{0.06;0.02;0.02},SUM((CumRev>b)*(CumRev-b)*r)))