Our commission structure is they get paid 10% of GP up to 40k.
20% of GP from 40k upwards but starting from 20k. If this happens they get 10% for the first 20k.
So if he sells a total of 60k GP his commission will be:
10% of 20k=2000
20% of 40k=8000
Total=10000
The problem comes in that he either has to sell 4 units or 40k GP. So if he sells 4 units and total 35k he will get paid
10% of 20k = 2000
20% of 15k = 3000
I have a formula to work out the first part, where C20 is his total GP
=IF(C20<40000,10%*C20,(10%*19999)+(20%*(C20-19999)))
But how do I add the number of units condition?
The formula I use to retrieve the GP for each salesman is, where C1 is the salesmans name, AH range is the column where the sales are captured by salesman name and AE is the column is the GP.
=SUMIFS(StockSheet!$AH:$AH,StockSheet!$AH:$AH,">0",StockSheet!$AE:$AE,C1)
Hope this makes sense.
20% of GP from 40k upwards but starting from 20k. If this happens they get 10% for the first 20k.
So if he sells a total of 60k GP his commission will be:
10% of 20k=2000
20% of 40k=8000
Total=10000
The problem comes in that he either has to sell 4 units or 40k GP. So if he sells 4 units and total 35k he will get paid
10% of 20k = 2000
20% of 15k = 3000
I have a formula to work out the first part, where C20 is his total GP
=IF(C20<40000,10%*C20,(10%*19999)+(20%*(C20-19999)))
But how do I add the number of units condition?
The formula I use to retrieve the GP for each salesman is, where C1 is the salesmans name, AH range is the column where the sales are captured by salesman name and AE is the column is the GP.
=SUMIFS(StockSheet!$AH:$AH,StockSheet!$AH:$AH,">0",StockSheet!$AE:$AE,C1)
Hope this makes sense.