I am looking to create a commission based formula

[Nick1966]

Hi

I am looking to create a commission based formula.

It needs to work something like this.

No commission is paid until 5% growth is achieved. So lets assume that last years sales were £20k. 5% growth means £300 commission would be paid once they achieved £21k in the month. For every % point over 5% they receive an additional £60. This is paid right up to 30% growth.

I hope someone can help.

Regards

Nick

[José Augusto]

Try these
=INT((C2-B2)/B2>=6%)*60+300*((C2-B2)/B2>=5%)

Please Login or Register  to view this content.

Where B2 is the last years sales and C2 the actual sales

[Nick1966]

Hi Jose
Thanks for reply. I have used the formula but it only seems to add the 60 and 300 figures together and put £360 in the total.

What I want is that if they grow 5% its £300
If they grow 6% its £360
If they grow 7% its £420
If the grow 8% its £480 etc

Regards

Nick

[José Augusto]

Try these
=300*((C2-B2)/B2>=5%)+((C2-B2)/B2>=5%)*INT(100*((C2-B2)/B2-5%))*60

Please Login or Register  to view this content.

[brokenbiscuits]

=if((c3-b3)/b3<0.05,0,if((c3-b3)/b3>0.3,0.3*100*60,(c3-b3)/b3*100*60))
?

[Nick1966]

Jose

Perfect - Thank you very much!!

[brokenbiscuits]

Nick, just to point out, Jose's formula omits the fact that you only want to pay commission up to 30% growth. The way you worded it I would expect a max commission value of £1,800? (£60*30)

[José Augusto]

Nick, just to point out, Jose's formula omits the fact that you only want to pay commission up to 30% growth. The way you worded it I would expect a max commission value of £1,800? (£60*30)

Yes, I forget this fact. So the correction

Please Login or Register  to view this content.

Regards

[shg]

Or

=(C2/B2>=105%)*(300+60*MIN(25,INT(C2/B2%-105)))

[Nick1966]

Thank you to everyone who has helped. The correction works fine now. Thanks Jose. Also thanks to 'brokenbiscuits' for pointing this out.

[Nick1966]

Thanks brokenbiscuits. Your point has now been corrected. i appreciate you pointing it out.