Help Required to create a sliding scale of commission based on variable information

Hi,

Really hope you can help

I am trying to create an excel file based on a commission rate that varies on sale amount.

The sliding scale is based on the following details

Purchases per month Commission Retainer
0-300 0% £500.00
301-1000 10% £500.00
1001-2000 15% £400.00
2001-3000 20% £300.00
3500+ 20% £0.00

** Purchases per month relates to individual purchase amount, Retainer rate is an additional payment of £500 which reduces based on the commission levels reached so if they reached 3500 they would not get a retainer at all, instead the money made would be on a stepped approach in relation to the commission bands above.

The commission percentage would be a percentage of 2 other variables that would be set e.g. i would enter the purchase amount which could be 350 and this equated to £1750 (so the everage £ made per purchase would be £5). It would then mean that the 1st 300 purchases would have no commission, however the next 50 purchases would have a 10% of commission giving £25 of commission (10% of 50 x £5) plus the retainer of £500

Hopefully this makes sense, i think it is quite similar to tax scales however i'm unsure how to do this because of the variable we have for purchases and the money made which would vary, plus the fact that the commission rate is in relation to purchases however the commission made is in relation to the sale price average.


Many thanks
Chris

Hi thanks very much fr your response, really appreciated

I have given a draft of what I’m trying to create where there would be two pieces of information entered manually by me each month based on the full value of sales and the amount of purchases received to equate to the sale value amount, this is in areas C10 and D10. Cells E10 and F10 would automatically be generated based on the manual input made in C10 and D10 along with the parameters or thresholds described.

Hopefully this makes sense.

Regards
Chris