Commission Calculation

[johnb1979]

Hi, I'd like help with a formula when creating a new commission calculation please.

I have 3 columns - Column 1 = £contract amount, Column 2 = contract length (in years), Column 3 = £commission.

The salesperson will have a hurdle of £2000 total/combined sales. When they hit £2000 they get a flat rate of £120. For every £ over £2000 they will get 6% of the contract amount for a 1 year deal and 18% for every £ on a 3 year deal (please see example below).

What I'd like is for a formula that only adds the commission for every £ over £2000 (total/combined sales, not just the individual deals) - in the example below the commission is calculated even though the contract amount is £1500.

Please help !!!

Thanks in advance - if you need any clarification regarding my query then please just let me know.

CONTRACT AMOUNT	CONTRACT LENGTH	COMMISSION
£500.00	3	£90.00
£500.00	1	£30.00
£500.00	3	£90.00
		£0.00
		£0.00
		£0.00

 

[johnb1979]

Thanks @Smudger_101 but it still doesn't seem to be working...

Would it help if I posted some examples of what it should look like (I'll just manually fill out a form)???

 

[johnb1979]

Hi all,

Please see below - these are some examples of what the finished sheet would look like - hope it makes sense. Please note that the contract amount could vary drastically from £1 to several thousands.

Really appreciate your help with this - as you can probably tell, I'm an Excel novice

 

[Marcelo Branco]

Maybe something like this

Book1
	A	B	C
1	CONTRACT AMOUNT	CONTRACT LENGTH	COMMISSION
2	100	1	0
3	500	3	0
4	250	3	0
5	300	1	0
6	650	3	0
7	200	1	0
8	200	3	36
Plan3

Cell Formulas
Range	Formula
C2:C8	C2	=MAX(0,MIN(SUM(A$2:A2)-2000,A2))*IF(B2=1,0.06,0.18)

Hope this helps

M.

 

[johnb1979]

BRILLIANT!!! Cracked it - thank you all so much for your support!!!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[Saba Sabaratnam]

Hi Marcelo,

That is a simple and brilliant solution. I have learned another method to solve Excel formula problems.

Kind regards

Saba

 

[Marcelo Branco]

Hi Saba,

Thank you for your kind words.

M.