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

This is what I'm looking for

if $Running Total$ is over $target$ and $Running Total Units$ is over $Target Units$, return $margin*.4 - OR - if $Running Target$ is less than $Target$ and $Maxi / R&M$ is 0 return $margin*.1 otherwise its $Margin*.2

You might have to explain it in a different way...

I'm looking at the September sheet but I can't work out what you are trying to do, perhaps you could comment the cells, and the results you want in the spreadsheet?

Are you trying to work out the "Commission" column?

I see, you triple posted this and have since got some replies... -_-

Yeah it's the commission I'm trying to get working.

Just explaining this one is a difficult task in itself.

I'm getting near to a solution thanks to everyone's help. I'll attach my new workbook for you to have a look.

The problem now may be with the way the running totals are calculated.

The deal is, any profit 'over' target is paid at 40% once the unit target has been exceeded.

At the min once both running totals are over target the commission changes to 40% but say for instance the target is 100,000 and 100 units.

And 120,000 and 120 units was achieved, it would be 10% or 20% of 100,000 depending on contract state on or off, up to 100,000 and 40% of everything after.

So a base from 10,000 to 20,000 plus 40% of anything over target so 20,000 x.4 = 8000

So it would b between 18,000 and 28,000 in total.

At the minute my formula is checking previous month end running totals to check against target, I'm not sure I'm even approaching this one from the right angle.