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

 

[LouisT]

So in your example, if i read your requirement correctly, the Commission should be Zero? Additionally, are all 3 of these contracts, One total contract or is the formula supposed to look at a sum of all 3 contracts?

 

[johnb1979]

@LouisT thanks for your reply. You are correct in that the commission should be zero until the contract amount reaches £2000 in total. Once it reaches £2000 a flat rate of commission of £120 should be added to the sales persons commission. For every £ over £2000, the sales person should then receive 6% for every £ of a 1 year contract and 18% for every £ of a 3 year contract.

Each row of the 'contract amount' column is a separate contract - in the example there are 3 separate contracts, each for £500.

I hope this helps but please let me know if you require any additional information.

Thanks again for your help

 

[Smudger_101]

Would this work for you? A2=Contract Amount; B2=Contract Length

'=IF(A2>=2000,SUM(120+(SUM(A2-2000))*(SUM(B2*6%))),0)'

 

[johnb1979]

Almost...thanks for trying! I think it's my fault for not explaining it properly. Maybe if I just explain the scenario it might be easier;

A salesperson has a target of £2000 a month to hit in contracts. This could be made up of any sized(£)/quantity of contracts. The spreadsheet is to be used for the salesperson to both track their progress through the month and also to calculate their commission.

The salesperson will earn £0 commission unless they reach a minimum of £2000 in the month (however many contracts that might be). Once they reach £2000, they will be paid a flat rate of £120 (this figure will appear in another sell on the spreadsheet - not in the 'commission' column). For every £ over £2000, the sales person should then receive 6% for every £ of a 1 year contract and 18% for every £ of a 3 year contract - the value shown in the commission column should always read 0 for every contract that is sold under the £2000 threshold - then once the combined sales total hits a minimum of £2001, the additional % (6% or 18%, whichever applies), is added.

I hope this helps and apologies if I'm making hard work of this but I do really appreciate you help - thank you

 

[Saba Sabaratnam]

Hi,

Based on my understanding of your requirement, I use the following formula in C2 to calculate total commission:

=IF(SUM($A$3:A3)>2000,IF(B3=3,A3*0.18,A3*0.06),"")

CONTRACT AMOUNT	CONTRACT LENGTH	COMMISSION
500​	3​
500​	1​
500​	3​
500​	3​
500​	1​	30​
500​	3​	90​
Total		120​

Kind regards

Saba

 

[johnb1979]

Thank you @Saba Sabaratnam , really appreciate your input - your formula is almost there! Please see table below - you can see in the 2nd row that once the total exceeds £2000 it adds the relevant % into the commission column, however, what I need the formula to do is disregard the first £2000 and just add the % for the amount over £2K (in this example it should only add the 0.18p for the £1 over £2K, not the £180.

Once this is fixed the rest of your formula works perfectly for my application - thank you!

 

[Saba Sabaratnam]

No problem

CONTRACT AMOUNT	CONTRACT LENGTH	COMMISSION
1000​	3​
1001​	3​	0.18​
500​	3​	90​
1000​	1​	60​
1000​	3​	180​
Total		330.18 ​

- Enter the IF(SUM($A$3:A3)>2000,IF(B3=3,(SUM($A$3:A3)-2000)*0.18,(SUM($A$3:A3)-2000)*0.06),"") in C2
- Enter =IF(SUM($A$3:A4)>2000,IF(SUM($A$3:A3)<2000,IF(B4=3,(SUM($A$3:A4)-2000)*0.18,(SUM($A$3:A4)-2000)*0.06),IF(B4=3,(SUM($A$3:A4)-SUM($A$3:A3))*0.18,(SUM($A$3:A4)-SUM($A$3:A3))*0.06)),"") in C3 and copy it down

Hope that it will meet your requirement.

Kind regards

Saba

 

[johnb1979]

Thanks @Saba Sabaratnam ...something not quite right with that I'm afraid - the calculations seem to be offset?

Sorry to be a pain but would you mind checking again please?

Thank you

 

[Smudger_101]

- Enter the IF(SUM($A$3:A3)>2000,IF(B3=3,(SUM($A$3:A3)-2000)*0.18,(SUM($A$3:A3)-2000)*0.06),"") in C3
- Enter =IF(SUM($A$3:A4)>2000,IF(SUM($A$3:A3)<2000,IF(B4=3,(SUM($A$3:A4)-2000)*0.18,(SUM($A$3:A4)-2000)*0.06),IF(B4=3,(SUM($A$3:A4)-SUM($A$3:A3))*0.18,(SUM($A$3:A4)-SUM($A$3:A3))*0.06)),"") in C4 and copy it down