Formula for commission structure

[ronie85]

Hi, I would like a formula for a commission structure.
Say C5 to C16 are months of the year with sales figures per month for which commission is paid. I would like the formula to work out the commission I would get each month.
The end of the formula on months after January will then minus the paid amounts of previous months though I can add this in myself.

The commission structure is below for which I would earn 0% on all sales up to 250,000.00, 1.25% on sales between 250,000.01 to 400,000.00 and so on.

It seems a bit complex and I hope to have it as 1 formula as I am currently using numerous formulas to calculate this.
Your help is greatly appreciated.

 

[Dave Patton]

Is your commission calculated on each sale of on the cumulative total?
It will help if you post an extract of your information with the forum's tool named XL2BB.
Please show the brackets and rates and the sample result for one month.
I am travelling and creating your sheet will take time.
I have posted solutions to similar questions.

 

[MARK858]

Maybe something like the below (change A1 to the cell reference with the sales total)'

Late Edit because of stupidity on my part

Book1.xlsb
	A	B	C	D
1	250000			0
2	250100			1.25
3	400000.00			1875
4	575000.00			4587.5
5	500000.00			3425
6	800000.00			8525
Sheet3

Cell Formulas
Range	Formula
D1:D6	D1	=SUMPRODUCT(--(A1>{250000;400000;575000;800000}), (A1-{250000;400000;575000;800000}), {0.0125;0.003;0.002;0.0025})

Edit: If not what you require then please answer the questions @Dave Patton has asked in post number 2 (and please [if possible] do use XL2BB as Dave has suggested for posting excerpts from a spreadsheet rather than an image, as we can't copy/paste it into a sheet)

 

[Dave Patton]

Please specify all the rates that are applicable and what version of Excel that you are using.

 

[Dave Patton]

Try the alternative(s) that you prefer.

With older versions of Excel, use SumProduct.
E10 uses the Lamba feature of Excel to build the formula. It can be extended to show the monthly amount.

With Name Manager
in New part enter a name for the new function (something relevant); I just used Commission_a.
in value part enter =LAMBDA(CumRev,LET(b, {0;250000;400000;575000;800000}, r, {0;0.0125;0.003;0.002;0.0025}, SUM((CumRev > b) * (CumRev - b) * r)))

The function specifies the necessary parameter "CumRev"

Commissions 2023.xlsm
	A	B	C	D	E	F
1	Brackets	Rates
2
3	0	0.00%	0.00
4	250,000	1.25%	1,875.00
5	400,000	1.55%	2,712.50
6	575,000	1.75%	3,937.50
7	800,000	2.00%	0.00
8	1.E+35
9
10	Amount	800,000.00	8,525.00	8,525.00	8,525.00	8,525.00
2d

Cell Formulas
Range	Formula
C3:C7	C3	=MIN($B$10-A3,A4-A3)*B3
A8	A8	=10^35
C10	C10	=SUMPRODUCT(--(B10>A3:A7),B10-A3:A7,B3:B7-B2:B6)
D10	D10	=SUM((B10>A3:A7)*(B10-A3:A7)*(B3:B7-B2:B6))
E10	E10	=Commission_a(B10)
F10	F10	=SUM(C3:C7)

 

[Dave Patton]

Commissions 2023.xlsm
	C	D	E
1
2
3
4		Sales	Commission
5	January	180,000.00	0.00
6	February	100,000.00	375.00
7	March	150,000.00	1,965.00
8	April	100,000.00	1,550.00
9	May	100,000.00	1,660.00
10	June	120,000.00	2,100.00
11	July	100,000.00	1,875.00
12	August	100,000.00	2,000.00
13	September	125,000.00	2,500.00
14	October	250,000.00	5,000.00
15	November	100,000.00	2,000.00
16	December	100,000.00	2,000.00
17		1,525,000.00	23,025.00
18
19	Total	1,525,000.00	23,025.00
20
2d

Cell Formulas
Range	Formula
D7	D7	=D6+50000
E5:E16	E5	=Commission_a(SUM($D$5:D5))-SUM($E$4:E4)
D17:E17	D17	=SUM(D5:D16)
D19	D19	=D17
E19	E19	=Commission_a(D19)

 

[Dave Patton]

Commissions 2023.xlsm
	C	D	E
1
2
3
4		Sales	Commission
5	January	180,000.00	0.00
6	February	100,000.00	375.00
7	March	150,000.00	1,965.00
8	April	100,000.00	1,550.00
9	May	100,000.00	1,660.00
10	June	120,000.00	2,100.00
11	July	100,000.00	1,875.00
12	August	100,000.00	2,000.00
13	September	125,000.00	2,500.00
14	October	250,000.00	5,000.00
15	November	100,000.00	2,000.00
16	December	100,000.00	2,000.00
17		1,525,000.00	23,025.00
18
19	Total	1,525,000.00	23,025.00
2d

Cell Formulas
Range	Formula
E5:E16	E5	=Commission_a(SUM($D$5:D5))-SUM($E$4:E4)
D17:E17	D17	=SUM(D5:D16)
E19	E19	=Commission_a(D19)

 

[ronie85]

Hi All, thanks for this. @MARK858 the =SUMPRODUCT(--(A1>{250000;400000;575000;800000}), (A1-{250000;400000;575000;800000}), {0.0125;0.003;0.002;0.0025}) works however could you explain why the 1.25%, 1.55%, 1.75% and 2% is in the formula as {0.0125;0.003;0.002;0.0025} ?
Although it works I don't understand it so that I can apply it to future commission structures of different percentages.

 

[ronie85]

Hi All, thanks for this. @MARK858 the =SUMPRODUCT(--(A1>{250000;400000;575000;800000}), (A1-{250000;400000;575000;800000}), {0.0125;0.003;0.002;0.0025}) works however could you explain why the 1.25%, 1.55%, 1.75% and 2% is in the formula as {0.0125;0.003;0.002;0.0025} ?
Although it works I don't understand it so that I can apply it to future commission structures of different percentages.

I get it now, its the differences in the percentages!

 

[MARK858]

@ronie85 although you were looking for a single formula. I believe you would be better off looking at post 5 by @Dave Patton (where he is using a table for the brackets and rates), as it it much easier to maintain if your brackets or commission rates change (as they normally do in my experience)