Bonus based on percent achieved

[ceytl]

I have searched all over and can't find the right formula.

I need to calculate a bonus based on the percent earned.

Below 95%: No Bonus
95%: 500
96%: 700
97%: 900
98%: 1,100
99%: 1,300
100%: 1,500

For every 1% over 100% gets a $100 (1% = $100)
example 101% gets 1,600, 102% gets $1,700
There is no cap on the 1%

Any help would be much appreciated.

 

[dermie_72]

assuming your precentage score is in A1 and the bonus is in B1

you could use the following formula.

=IF(A1<0.95,0,IF(A1<0.96,500,IF(A1<0.97,700,IF(A1<0.98,900,IF(A1<0.99,1100,IF(A1<1,1300,IF(A1=1,1500,(((A1-1)*100)*100)+1500)))))))

 

[albertc30]

Hi.

Say cell A1 is percentage but represented without the % or in other words formatted as number;

Place the code in cell B1

=(A1-100)*100+1500

Hope it's a start.

Regards,
Albert

 

[ceytl]

assuming your percentage score is in A1 and the bonus is in B1

you could use the following formula.

=IF(A1<0.95,0,IF(A1<0.96,500,IF(A1<0.97,700,IF(A1<0.98,900,IF(A1<0.99,1100,IF(A1<1,1300,IF(A1=1,1500,(((A1-1)*100)*100)+1500)))))))

How would I round up or down on the 1% part?

Right now if it's 101.5% it shows $1,650, but I want it to round to $1,700

Thanks for your help so far!

 

[dermie_72]

try adjusting the formula to:
=IF(A1<0.95,0,IF(A1<0.96,500,IF(A1<0.97,700,IF(A1<0.98,900,IF(A1<0.99,1100,IF(A1<1,1300,IF(A1=1,1500,((ROUNDUP((A1-1),2)*100)*100)+1500)))))))