Formula for tiered commission on different quantity/price levels

[Mak1]

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:

• 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.

 

[KRice]

I was surprised at the complexity of the plan, with a baseline commission rate dependent on sales price, a commission factor dependent on sales volume, and a tiered structure for sales volume. All of those ultimately lead to more tangible commissions per units sold, but a lot of tedious math is involved.