Commission Payable

[Reedster]

Hello All,

I know there are very many questions regarding Commission's. I have had a look through some of the existing ones for an answer but cant seem to find what i am looking for.

We have started a new commission structure at work which is cumulative i have managed to get all the formula in place for calculating it which work fine.

However the new structure is this - the salesperson gets paid on money that goes through the till. eg £1000 = 1% = £10 commission
This is then calculated over the month and they will get a grand total.
Sometimes refunds get given after the commission period has ended this doesnt matter normally as the refund rate is low and it comes off their total for the period they are in so it all works out so the commission is almost always a positive value.

I got to thinking if they had a really bad month though and the commission value actually became negative how would we deal with that? We cant take money off their Basic Salary so we would have to withhold any postive commission they earned in the future until the negative balance was paid off. I hope that makes sense

Excel Workbook
G
H
I
1
Monthly Total
Monthly Commission
Amount To Pay
2
40,000.00
600.00
600.00
3
-30,000.00
-450.00
0.00
4
20,000.00
300.00
0.00
5
20,000.00
300.00
150.00
6
0.00
0.00
7
0.00
0.00
8
0.00
0.00
9
0.00
0.00
10
0.00
0.00
11
0.00
0.00
12
0.00
0.00
13
0.00
0.00
14
50,000.00
750.00
15
Designers
Excel 2007

In the example have shown, the "amount to pay" column is manually entered but it is that column that i would like to calculate automatically.

I hope you can help.

Kind Regards
Barry

 

[VoG]

Try as pseudoformula

commission=max(0,your formula)

 

[Reedster]

Simpy Brilliant!!

Thankyou very very much indeed.

Best Wishes
Barry