Bonus Calculation

[jonwool]

I need cell E3 to show the bonus amount my staff earn based on the following:

If cell H27 is equal to or greater than B4 then they qualify for a bonus payment based on the below calculation.

If H27 is 100-149% of B4 then they earn 7.5% of H27
If H27 is 150-199% of B4 then they earn 12.50% of H27
If H27 is 200-249% of B4 then they earn 17.50% of H27
If H27 is 250%+ of B4 then they earn 22.50% of H27

I hope this helps.

Many thanks for your help in advance

Jon

[alansidman]

=if(And(h27>=b4,h27<1.5*b4),h27*.075,if(and(h27>1.5*b4,h27<2*b4),.125*h27,if(and(h27>2*b4,h27<2.5*b4),.175*h27,.225*H27))))

Try the above. There is no consideration in my formula for not reaching at least 100%. Does this need to be included?

[jonwool]

hi thats great. yes there does need to be consideration if they dont hit 100% i.e. if they dont hit 100% then the bonus payment to them is zero? can you biuld this in please. Thanks for your help

[Ace_XL]

Try

Please Login or Register  to view this content.

[Kevin UK]

Hi jonwool

See the attached example using IF & VLOOKUP functions, cells G1:H5 hold the assumption table.