Hi there,
Im trying to create some formulas so me and my colleagues can work out our bonus. It comes in two parts - Team and Individual accelerators. Now Im able to get the individual accelerators to work to a certain point then all I get is "FALSE" and I can get the Team bonus to only work if we over achieve target (where we get a team accelerator) - this is the most difficult bit of the equation for me. I'm wondering if someone could see where the errors are and tell me how I get it to show the correct ammount:
Individual:
=IF(C2<68,0,IF(C2>=68,50,IF(C2>=74,100, IF(C2>=80,200, IF(C2>=86,500)))))
So if I am under 68 I get nothing, if over 68 then £50 (this works and if I put C2>=68<74 and so on I still get the same issue after this amount).
Team:
=IF(Team!C3=100%,B2,IF(Team!C3>=90%<100%,B2*80%,IF(Team!C3>=80%<90%,B2*60%,IF(Team!C3<80%,0,IF(Team!C3>100%,(((B2*Team!C3)-B2)*2.5)+B2)))))
So this is probably the most complex formula I've done but it works the same way as the individual one but in percentage to target (we are sales). Now the last part of the formula works in this one i.e. if we achieve over 100% of volume we get 100% of our team bonus pot plus 2.5% extra for every 1% above 100 (so 110% of volume means 125% of bonus pot), between 90-100 its 80% of put and between 80-90% its 60% of pot and below that then nothing.
TEAM!C3 is the team % to volume target.
Im not that adept at spreadsheets (well not as much as I used to be since I hardly have to create them now), so If clarification is needed please say so.
If I am completely and utterly wrong, be nice and gentle....
Im trying to create some formulas so me and my colleagues can work out our bonus. It comes in two parts - Team and Individual accelerators. Now Im able to get the individual accelerators to work to a certain point then all I get is "FALSE" and I can get the Team bonus to only work if we over achieve target (where we get a team accelerator) - this is the most difficult bit of the equation for me. I'm wondering if someone could see where the errors are and tell me how I get it to show the correct ammount:
Individual:
=IF(C2<68,0,IF(C2>=68,50,IF(C2>=74,100, IF(C2>=80,200, IF(C2>=86,500)))))
So if I am under 68 I get nothing, if over 68 then £50 (this works and if I put C2>=68<74 and so on I still get the same issue after this amount).
Team:
=IF(Team!C3=100%,B2,IF(Team!C3>=90%<100%,B2*80%,IF(Team!C3>=80%<90%,B2*60%,IF(Team!C3<80%,0,IF(Team!C3>100%,(((B2*Team!C3)-B2)*2.5)+B2)))))
So this is probably the most complex formula I've done but it works the same way as the individual one but in percentage to target (we are sales). Now the last part of the formula works in this one i.e. if we achieve over 100% of volume we get 100% of our team bonus pot plus 2.5% extra for every 1% above 100 (so 110% of volume means 125% of bonus pot), between 90-100 its 80% of put and between 80-90% its 60% of pot and below that then nothing.
TEAM!C3 is the team % to volume target.
Im not that adept at spreadsheets (well not as much as I used to be since I hardly have to create them now), so If clarification is needed please say so.
If I am completely and utterly wrong, be nice and gentle....
