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

 

[DeficientOptimism]

For your tier structures - wouldn't it be for example 4001 Euro to 17,000 Euro for 10% and not 4000 which was the maximum of the previous tier?

 

[Chris1289]

For your tier structures - wouldn't it be for example 4001 Euro to 17,000 Euro for 10% and not 4000 which was the maximum of the previous tier?

Yes, I guess 4,000.01 to 17,000 would be the best way to do so

 

[Chris1289]

For your tier structures - wouldn't it be for example 4001 Euro to 17,000 Euro for 10% and not 4000 which was the maximum of the previous tier?

Thinking about this, the previous IF formula didn't work on the basis of 4,000.01 or 4001 and that seemed to work ok?

 

[Chris1289]

Bump for help please

 

[DeficientOptimism]

Min	Max		Sales	Monthly Commission
-	4,000.00	0%​	17,000.00	3,900.00 =IF(D2<=B2,(D2*C2),IF(AND(D2>=A3,D2<=B3),((D2-A3)*C3),IF(D2>=A4,((B3-A3)*C3)+(D2-A4)*C4)))
4,000.01	17,000.00	10%​
17,000.01		20%​

This should work - assume A1 is the first cell "Min" and go from there to recreate. You can use the same logic on quarterly.

 

[DeficientOptimism]

Min	Max		Sales	Monthly Commission
-	4,000.00	0%​	17,000.00	3,900.00 =IF(D2<=B2,(D2*C2),IF(AND(D2>=A3,D2<=B3),((D2-A3)*C3),IF(D2>=A4,((B3-A3)*C3)+(D2-A4)*C4)))
4,000.01	17,000.00	10%​
17,000.01		20%​

This should work - assume A1 is the first cell "Min" and go from there to recreate. You can use the same logic on quarterly.

The cell 3900 =IF(D2<=B2,(D2*C2),IF(AND(D2>=A3,D2<=B3),((D2-A3)*C3),IF(D2>=A4,((B3-A3)*C3)+(D2-A4)*C4)))

 

[DeficientOptimism]

Min	Max		Sales	Monthly Commission
-	4,000.00	0%​	17,000.00	3,900.00 =IF(D2<=B2,(D2*C2),IF(AND(D2>=A3,D2<=B3),((D2-A3)*C3),IF(D2>=A4,((B3-A3)*C3)+(D2-A4)*C4)))
4,000.01	17,000.00	10%​
17,000.01		20%​

This should work - assume A1 is the first cell "Min" and go from there to recreate. You can use the same logic on quarterly.

The sales figure should be 30,000 = not 17,000

 

[Chris1289]

Min	Max		Sales	Monthly Commission
-	4,000.00	0%​	17,000.00	3,900.00 =IF(D2<=B2,(D2*C2),IF(AND(D2>=A3,D2<=B3),((D2-A3)*C3),IF(D2>=A4,((B3-A3)*C3)+(D2-A4)*C4)))
4,000.01	17,000.00	10%​
17,000.01		20%​

This should work - assume A1 is the first cell "Min" and go from there to recreate. You can use the same logic on quarterly.

Hi DeficientOptimism,

Thank you very much for that - I have been able to replicate the formula within my spreadsheet and it seems to work perfectly! I really appreciate your help with this.

All the best.

 

[BSALV]

you make a table "Tier" and the last column "cumul" is the commission you get with an invoice equal at "Tier minimum" of that row.
The orange table in F:G are 2 examples

Map4
	A	B	C	D	E	F	G	H
1	table
2	Tier Minimum	Tier Maximum	Commission Rate	cumul		invoice	commision
3	0 €	4.000 €	0,0%	0 €		5.000,0 €	100,0 €
4	4.000 €	17.000 €	10,0%	0 €		5.200,0 €	120,0 €
5	17.000 €	1.000.000.000 €	20,0%	1.300 €
6
Blad3

Cell Formulas
Range	Formula
G3:G4	G3	=VLOOKUP(F3,TBL_Tier,4,1)+(F3-VLOOKUP(F3,TBL_Tier,1,1))*VLOOKUP(F3,TBL_Tier,3,1)
F4	F4	=+F3+200
D3:D5	D3	=SUM(D2,IFERROR(SUM(B2,-A2)*C2,0))