Bonus Commission tiered scheme

[pmoore31]

Hi All,

I have tried to download EL2BB to upload a mini-sheet but I cant seem to install it correctly.

I wonder if anyone could help please, our bonus scheme is the following:-

The quarterly bonus will be paid at 1.5% of your turnover raised over £150,000 rising to 2% over £800k, 2.5% over £1M and 5% over £1.5M.

The estimated turnover will be 1st quarter £146,000 2nd quarter £180,000 3rd quarter £240,000 and 4th quarter 300,000

What confuses me is its you don't have to reach the £150,000 within the first quarter, its calculated on a 12 month division.

I have attached a screen shot that I have manged to calculate the first tier, but I have tried for weeks to get my head around the other percentages and how to create the working around them.

Can anyone please point me in the right direction please?

Thanks

Phil

 

[Micron]

Both of those images look the same to me. Are you looking for a formula or vba solution? Formulae are not my thing. In vba, I'd probably use something like
Sorry I can't be more explicit than that; I cannot follow your intent from the picture.

Select Case Turnover
  Case Is >=lower number here, Is <= highest number for the range here
     'do this math
  Case Is >= lower number of next range here, Is <= highest number for the range here
    'do this math
  Case Is ...

End Select

 

[pmoore31]

Both of those images look the same to me. Are you looking for a formula or vba solution? Formulae are not my thing. In vba, I'd probably use something like
Sorry I can't be more explicit than that; I cannot follow your intent from the picture.

Select Case Turnover
  Case Is >=lower number here, Is <= highest number for the range here
     'do this math
  Case Is >= lower number of next range here, Is <= highest number for the range here
    'do this math
  Case Is ...

End Select

Thank you, its was more for a formula, I really need to get my head around all of this and do more research. I didn't even know what a vba was.

 

[Micron]

Visual Basic for Applications - a programming language (not to be confused with VB aka VB6) for M$ applications. Not sure if it's best for what you want to do or not as Excel formulas are not my thing. If I wanted to do math on some value and the expression would be one of several possibilities based on some sort of input, I would find it much easier to code because of that limitation I have. Maybe you are familiar with it by a different name - a macro, which is not the same thing in all M$ applications.