Jeyanthan has attended:
Excel Intermediate course
Excel VBA Intro Intermediate course
Nested if formula
Hi,
Could you please help me to set up the if formula to calculate the bonus. The criteria for bonus is listed below.
Criteria Bonus H12>=X21 £200
H16>=X22 £300
If above both achived £750
If above both achived and occupancy above 95% £1000
Regards
Jey
RE: nested if formula
Hi Jey
Thanks for getting in touch. These complicated IF formulas are easier to look at if you start from the highest value and work your way down. In my example I have gone for the £1000 case.
Note you will have to substitute in the cell that contains your occupancy rate - I have put A1. I have also assumed if none of the criteria are met the bonus is zero.
=IF(AND(H12>=X21,H16>=X22,A1>95%),1000,IF(AND(H12>=X21,H16>=X22),750,IF(H16>=X22,300,IF(H12>=X21,200,0))))
I found it difficult to construct test data for your criteria so try it out and let me know if you have any problems.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
