Hi hope this makes sense.....
I need to work out commission on (1) quantity of product and (2) its sale price.
I would appreciate some suggestions and help on the 2 points below:
I need to work out commission on (1) quantity of product and (2) its sale price.
I would appreciate some suggestions and help on the 2 points below:
- The commission is 50% for any sales of products equal to or above £5 value and decreases by 0.1% for every penny less than £5.
The formula I have for this so far :
=((0.5-(5-C19)*0.1)*C19) where C19 is the sales price.
Can this be simplified or tidied up? Also how do I include it allow a sales price over £5 to stick at 50% commission?
- The above formula is for the first 5000 units. Thereafter every 1000 units sold the commission amount (result of formula from point above) is decreased/discounted by 1%.
What would the formula be, if the number of units sold is E19 and I wanted to show the total commission in F19?
For e.g.
A) If 7000 units were sold at £6.00, commission would be as follows:
1) 50% for the first 5000 units ( £3 x 5000 ) = £3000
2) (50% for the units 5001 to 6000 ; £3 x 1000) with a 1% discount = £2970
3) (50% for the units 6001 to 7000 ; £3 x 1000) with a 2% discount = £2940
TOTAL £8910
B) If 7000 units were sold at £0.60, commission would be as follows:
1) 6% for the first 5000 units ( £0.036 x 5000 ) = £180
2) (6% for the units 5001 to 6000 ; £0.036 x 1000) with a 1% discount = £35.64
3) (6% for the units 6001 to 7000 ; £0.036 x 1000) with a 2% discount = £35.28
TOTAL £250.92
Should you have any questions please let me know.
