Bonus Accelerator

Hi I am looking to find out how to lay out this excel formula, I am wanting to make a basic bonus accelator calculator,
I essentially want Excel to tell me what Bonus i will get when i hit a certain % over target for example - if i have a target of £3600 and if i went over by 10% i would get 10% of the amount i went over by plus my basic bonus
Below i have detailed my Information

TARGET ACTUAL 10% 20% 30% <% over Target
3600 5651.00 3960 4320 4356

If i dont get Target i want it to return "BASIC WAGE" which i can get it to return however i am running into difficulties when trying to evalute the Actual against the % I thought it was a basic IF Formula - but everytime it says my argument is too long see example below

=IF(D10<=C10,"Basic Wage",IF(D10=J10,D10-J10,M10*10%,IF(D10=K10,D10-K10,M10*20%,IF(D10=L10,D10-K10,M10*30%))))

I would appreciate any feedback on this - as its getting me really annoyed now -

Basically If i hit target but went over by 10%,20%,30% i would only get 10%,20%,30% of the diffrence and this would return me a figure!!

=IF(D10<=C10,"Basic Wage",IF(D10=J10,D10-J10,M10*10%,IF(D10=K10,D10-K10,M10*20%,IF(D10=L10,D10-K10,M10*30%))))

Another strange thing is in this piece of the formula:

IF(D10=J10,D10-J10......... will always be 0

The solution is fairly easy, so add attachment

Hi Guys,

Sorry if what i was trying to get across became confusing - i have now attached my Pipeline Document -

You will see there is 12 sheets + 1 Year to date sheet,

It is in the year to date sheet i am trying to calculate how much bonus will be paid..

So Information that the Bonus must show is -

Wither they will receive bonus or not to get bonus they must get target -

• If they go over target by 10% they will receive 10% of the amount which they went over target
• If they go over target by 20% they will receive 20% of the amount which they went over target
• If they go over target by 21% they will receive 30% of the amount which they went over target

If they do not hit bonus it will return and say Basic Wage

I am truly flumoxed with the coding and i know i am getting mixed up -
Please see the attached pipeline document and let me know if you can help

Many Thanks in advance

Mark Oliver

Hi There,

Thank you for the response,

I tried to implement your formula which you recommended however i think it may be backward -

I have inputed the formula but when say January was blank it would return back"900" which obviously is not the case..

And then when i went over target by 1900 it would return 0?

Which i know should be return approx - £381 + £666

So i have attached the work book again with the inputed formulae and the the ammendments i made in January.

I hope this helps

And thank you for your fast reply

Thank you for the fast reply..

I am now on your example just now -

Further to that - if i wanted to make a cell Total Bonus - where it would calculate any accelarated bonus + basic bonus

So if Target is hit < 10% i would get a bonus of £666.00 as standard see cell (D24)

but can i show - D24 + Accelarated Bonus,

But if i get under target i would just get Basic Wage?

is this a simple formulae also, see my revised excel workbook

Ok sorry for the confusion

If you look at the Year to Date sheet you will see below there is OTE (8k per year or £666)

So if i just hit target - say 3000 - i will get £666

if i go over 10% i will get the diffrence which you have calculated in the accelarated bonus bit plus 666

So i am now trying to incorprate in a total bonus cell (Target + Accelarated Bonus)

or it to show just Bonus if i do not hit over 10% etc.

Hopefully this is explained a bit better for you

Thanks in advance

Mark