Commission Calc

[Welsh Mark3]

I've read through lots of similar posts but they don't all seem to reflect what I'm trying to do. I'm building trying to build a sheet that calculates commission based on sales for a given month. Cell d2 would have monthly sales target, Cell E2 would have the sales result in F2 I would like to put the formula.

The model is as follows

• 15% commission of all revenue between 0% and 100% of monthly target only when 50% of monthly target is achieved. No commission is paid unless 50% of monthly target is achieved.
• 30% commission on all revenue between 100% and 150% of monthly target
• 35% commission paid on revenue above 150% of monthly target.

Appreciate your help

 

[Marcelo Branco]

Maybe

Pasta2
	D	E	F	G	H	I	J	K
1	Target	Sales	Commission		Sales/Target	Commission	Marginal Commission
2	100	40	0		0	15%	15%
3	100	60	9		1	30%	15%
4	100	100	15		1,5	35%	5%
5	100	120	21
6	100	160	33,5
7	180	220	39
8	180	300	64,5
9	180	100	15
10
Plan2

Cell Formulas
Range	Formula
J2:J4	J2	=I2-N(I1)
F2:F9	F2	=IF(E2/D2<0.5,0,SUMPRODUCT(--(E2/D2>H$2:H$4),E2-(H$2:H$4*D2),J$2:J$4))

Hope this helps

M.