Hello everyone, this is my first post I've bashed my brain and google for 8 hours and I'm completely stuck. Hopefully you can help.

I've included a link to the spreadsheet below.

I'll try to explain but it i'm not really sure how to start, it might get a bit John Connor *terminator paradox gag (sorry my brain is battered)


The sliding scale commission structure is as so

10% of margin
20% of margin if an R&M / Maintenance contract is achieved
40% of margin over target if annual unit target and annual profit margin is exceeded.

For example ON target earnings of 100 units with £100,000 margin would be a minimum of £10,000 and Maximum of £20000 depending on the amount of R&M / Maintenance contracts achieved.

However if the salesman achieves over target on both units and margin then everything over this would be paid at 40% regardless of R&M / Maintenance contract.

In effect on 120,000 margin based on 101 units the salesman would be paid 40% of the £20,000 equal to £8000. Everything up to, and including the target would be paid at 10 or 20% depending on whether an R&M / Maintenance contract are achieved.

On the September tab I have attempted to butcher an IF OR AND command I found online to fit my requirements but to no avail. Also I have tried to put it in some sort of sudo terms what I'm trying to achieve.

If you need any further clarification please let me know.

Thank you in advance for any help.

Andy


http://www.stevechapples.co.uk/THESPREADSHEET.xls