So I have a formula I'm trying to build to track my bonus at work. Right now, the formula looks like this:
=IF((C4/B4)>=90%,
IF(G4<4.99%,500,
IF(AND(G4>=5%,G4<=5.99%),450,
IF(AND(G4>=6%,G4<=6.99%),300,
IF(AND(G4>=7%,G4<=7.99%),250,
IF(AND(G4>=8%,G4<=8.99%),100,0)
)
)
)
),0)
So essentially my formula looks at the error rate I have, checks whether or not I've made at least 90% of my goal, and returns a bonus amount based on that criteria. However, my bonus structure allows that for every 5% above 100% I receive, I get an extra 50. I'm wondering what I would have to do to modify my current formula (above) so that, if my percentage to goal is 105% at an error rate of less than 5%, it returns 600, 110% at a rage of 7.59% returns 450, and 135% at an error rate of 6.99% returns 1000.
Thank you!
=IF((C4/B4)>=90%,
IF(G4<4.99%,500,
IF(AND(G4>=5%,G4<=5.99%),450,
IF(AND(G4>=6%,G4<=6.99%),300,
IF(AND(G4>=7%,G4<=7.99%),250,
IF(AND(G4>=8%,G4<=8.99%),100,0)
)
)
)
),0)
So essentially my formula looks at the error rate I have, checks whether or not I've made at least 90% of my goal, and returns a bonus amount based on that criteria. However, my bonus structure allows that for every 5% above 100% I receive, I get an extra 50. I'm wondering what I would have to do to modify my current formula (above) so that, if my percentage to goal is 105% at an error rate of less than 5%, it returns 600, 110% at a rage of 7.59% returns 450, and 135% at an error rate of 6.99% returns 1000.
Thank you!