Struggling with a tiered commission structure (monthly & quarterly)

[Chris1289]

Hi all,

Help would be greatly appreciated as I'm an Excel novice. I've recently moved to a new recruitment agency where Excel isn't used fully, with commission calculations being done manually. We have a tiered commission structure monthly and quarterly, so I'm trying to create a spreadsheet to keep an eye on my figures etc.

Monthly

Tier Minimum	Tier Maximum	Commission Rate
€0	€4,000	0%
€4,000	€17,000	10%
€17,000		20%

Quarterly

Tier Minimum	Tier Maximum	Commission Rate
€0	€40,000	0%
€40,000	€57,000	10%
€57,000		15%

To clarify the above table, if I invoiced €30,000 in a month:

• I wouldn't earn any commission on the first €4,000 (€0)
• Then 10% on any billing between €4,000 and €17,000 (€1,300)
• Then 20% on anything above €17,000 (€2,600)
• Total commission for that month would be €3,900

At my previous company, I already had access to a formula that helped me calculate monthly commission on their tiered commission structure, which I am trying to emulate, however I can't seem to get to work - this was the previous formula (taken from a random month, so D7 refers to the total value of sales invoiced that month): =IF(D7<=10000,10%*D7,IF(D7<=18000,10000*10%+20%*(D7-10000),IF(D7<=25000,10000*10%+8000*20%+30%*(D7-18000),10000*10%+8000*20%+7000*30%+35%*(D7-25000))))

How can I emulate the above formula for my new Monthly and Quarterly commission structures? Would sticking with an IF formula be better than VLOOKUP?

Many thanks,

Chris

 

[Dave Patton]

Commission2022.xlsm
	A	B	C	D
1	Total sales	30,000.00
2	Commission	3,900.00	3,900.00
3
4
5	Brackets	Rates	--- Arithmetic ---
6	cell b7 is blank		By Bracket	Cumulative
7	0	0%	0.00	0.00
8	4,000	10%	1,300.00	1,300.00
9	17,000	20%	2,600.00	3,900.00
10
1b

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:D9	D7	=MAX(0,MIN($B$1,A8)-A7)*B7+N(D6)

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;4000;17000}
- The array of Rate Differentials is named aR Refers to ={0;0.1;0.1}
Advantages
The formula is easier to read.
The table is not required if the formula C2 is used.
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.

 

[Dave Patton]

Welcome to the forum.
The formula uses named information (see post 11) for the Bracket data and the Rate Differentials data.
Use Formulas Name Manager to add the names and respective data.
The double negative -- coerces the True or False to 1 or 0 for the formula.
The data below presents the results for the Quarters.

Commission2022.xlsm
	A	B	C
12	Total sales - Quarter	200,000.00	23,150.00
13	Total sales - Quarter	100,000.00	8,150.00
14	Total sales - Quarter	50,000.00	1,000.00
15
1bb

Cell Formulas
Range	Formula
C12:C14	C12	=SUMPRODUCT(--(B12>aBQ),B12-aBQ,aRQ)