Advanced Commision Calculator

[bromy2004]

Hi Everyone,

I'm trying to calculate the commission for work and I'm getting Really long formulas.

it works like this

Excel Workbook
	I	J	K	L	M	N
77	*	IF	*	*	*	*
78	*	GP	IF	*	*	*
79	*	0.25 on the total value	GP	IF	*	*
80	*	*	0.25 on 300000	GP>500000	* * * * * * * *240,000.00	*
81	*	*	+ 0.3 on the difference	0.25 on 300000	* * * * * * * *175,000.00	*
82	*	*	*	+ 0.3 on 200000	*	*
83	*	*	*	+ 0.4 on Total - 500000	*	*
84	*	*	*	*	*	*
85	*	*	*	*	*	*
86	*	Q1	Q2	Q3	Q4	*
87	Commision	150000	150000	150000	150000	*
88	Year To Date Total	150000	300000	450000	600000	*
89	*	37500	37500	45000	55000	175000
Sales & GP SP & BS

So
with the assumption that the person gets 150,000 in GP per Quarter
in Q1 they get 25% of 150,000 = 37,500
in Q2 they get 25% of 300,000 (Year to Date Total) Minus 37,500 = 37,500
in Q3 they get 25% of 300,000 plus 30% of 150,000 Minus 75,000 (commision paid Q1 and Q2) = 45,000
in Q4 they get 25% of 300,000 plus 30% of 200,000 plus 40% of 100000 Minus Commission paid (120,000) = 55,000

but all this needs to be done in 1 cell.

anyone up for the challenge??

-Bromy

 

[sanrv1f]

see if any of the solutions here help

http://www.mrexcel.com/pc17.shtml

 

[bromy2004]

Thank you for that link.

i looked through it,
however they only work on a single level commission...and I'm unsure how to convert them to a 3 levelled commission.

can you please show an example?