Thread: Bonus calculation using IF statement

Hi there,

I need to calculate the bonus to be received by an employee who has generated total sales of for example $14,150. The bonus scale is 10% for amounts between 10000-12000, 20% for amounts between 12000-14000 and 25% for amounts over 14000. It must be an IF statement. HELP!!

Thanks to anyone who's able to help in advance.

Excelbonus

I have a shorter formula

=IF(A1>14000,0.25,IF(A1>12000,0.2,IF(A1>10000,0.1,0)))*A1

Thanks to you both for the quick replies. Unfortunately neither solution seems to work. The employee in this example should get a total bonus of $637.50 ($200 = 10% for amounts between 10-12000 ($2000), $400 = 20% for amounts between 12-14000 ($2000) and $37.50 = 25% for amounts over 14000 ($150)).

Any other ideas?

Thanks again.

Hi, Is this any better ??

=+IF(AND(A6>=10000,  A6<12000), (12000-A6)*0.1,IF(AND(A6>=12000,A6<14000),200+(14000-A6)*0.2,IF(A6>=14000,600+(A6-14000)*0.25,"No Bonus")))

Regards Mick

This one you could have figured out yourself now. Formula remains the same

=IF(A8>14000;0,25*(A8-14000)+0,2*2000+0,1*2000;IF(A8>12000;(A8-12000)*0,2+0,1*2000;IF(A8>1000;(A8-10000)*0,1;0)))

Gr,
Ricardo

Thanks to rgrietveld, it works! Fantastic, now I'll be able to sleep again!!