Nested if formula

Resolved · Urgent Priority · Version 2007

Sun 24 Feb 2013, 15:24 Delegate Jeyanthan said...

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

Mon 25 Feb 2013, 01:15 Trainer Gary said...

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

Mon 25 Feb 2013, 09:43 Delegate Jeyanthan said...

RE: nested if formula

Thank you Gary,
It is working very well.

Regards
Jey

Excel tip:

Checking formulas with multiple operators

When dealing with formulas containing more than one operator (+, -, /, *), Excel follow standard BEDMAS order of operation rules. These rules specify the order that calculations will be performed in, regardless of how the formula reads left to right:

B = brackets
E = exponents
D = division
M = multiplication
A = addition
S = subtraction

It should be noted that multiplication and division are considered equal; as are addition and subtraction.

If you would like to check the order in which Excel is performing calculations in a formula, simply click on the cell containing the formula. Then go to Tools - Formula Auditing and select Evaluate Formula.

In the Evaluate Formula dialogue box that appears on your screen, click the Evaluate button to see how Excel calculates the formula result.

View all Excel hints and tips

Nested if formula

Nested if formula

RE: nested if formula

RE: nested if formula

Excel articles

Excel tip:

Checking formulas with multiple operators