Increase amount based on percentage formula?

[kitteyrin]

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!

 

[LiveToExcel]

Hi kitteyrin,

I believe I've arrived at a working solution for your problem. However, you will have to add a lookup table, as I'm using a VLOOKUP formula to solve this problem.

In my solution, I added the following lookup table to J1:K7 (headers included).

=IF(AND(C4/B4>=90%,G4<9%),VLOOKUP(G4,$J$2:$K$7,2)+IF(C4/B4>100%,ROUNDDOWN((C4/B4-1)/0.05,0)*100,0),0)

The data table that the VLOOKUP is referencing is at the bottom of the post.

I've assumed that no bonus will be paid if the % is less than 9%, whether or not the completion percentage is greater than or equal to 90%. This might be a faulty assumption, but this point was not clarified in the original question.

I've also assumed that the additional 5% of completion over 100% will add 100 to the bonus, as that is what you have shown in the examples listed in your first post (although 50 was stated).

Also, this assumes that the additional bonus amount (100) will only be applied in complete increments of 5% over 100% completion. So, a completion rate of 104% will not receive the additional bonus amount of 100.

Below is the data table entered into J1 in my solution, and beneath that is a simulation list of results obtained by the formula.

Hope this helps.

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]%
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Bonus
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]500
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]5%
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]450
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6%
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7%
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]250
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]8%
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]100
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9%
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 461"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Completion Basis[/TD]
[TD]Completion[/TD]
[TD] [/TD]
[TD]New Bonus[/TD]
[TD] [/TD]
[TD]Perccentage[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD] [/TD]
[TD="align: right"]500[/TD]
[TD] [/TD]
[TD="align: right"]4.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]95[/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD] [/TD]
[TD="align: right"]5.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD] [/TD]
[TD="align: right"]300[/TD]
[TD] [/TD]
[TD="align: right"]6.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]105[/TD]
[TD] [/TD]
[TD="align: right"]350[/TD]
[TD] [/TD]
[TD="align: right"]7.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD] [/TD]
[TD="align: right"]300[/TD]
[TD] [/TD]
[TD="align: right"]8.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]115[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]9.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD] [/TD]
[TD="align: right"]500[/TD]
[TD] [/TD]
[TD="align: right"]4.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]95[/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD] [/TD]
[TD="align: right"]5.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD] [/TD]
[TD="align: right"]300[/TD]
[TD] [/TD]
[TD="align: right"]6.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]105[/TD]
[TD] [/TD]
[TD="align: right"]350[/TD]
[TD] [/TD]
[TD="align: right"]7.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD] [/TD]
[TD="align: right"]300[/TD]
[TD] [/TD]
[TD="align: right"]8.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]115[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]9.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD] [/TD]
[TD="align: right"]500[/TD]
[TD] [/TD]
[TD="align: right"]4.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]95[/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD] [/TD]
[TD="align: right"]5.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD] [/TD]
[TD="align: right"]300[/TD]
[TD] [/TD]
[TD="align: right"]6.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]105[/TD]
[TD] [/TD]
[TD="align: right"]350[/TD]
[TD] [/TD]
[TD="align: right"]7.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD] [/TD]
[TD="align: right"]300[/TD]
[TD] [/TD]
[TD="align: right"]8.00%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]115[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]9.00%[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[kitteyrin]

That worked- thank you so much for the help!