A logical function tests a condition and returns a result depending on whether it is true. IF handles one test with two outcomes. AND and OR combine tests.
A nested IF handles more than two outcomes. The marks in this lesson are invented.
How do you choose the function?
Read the rule aloud and count the outcomes.
- Two outcomes, one test: use
IF. Form:=IF(test, result if true, result if false). - Two outcomes, every test must hold: put
AND(test1, test2)inside the IF. - Two outcomes, any test is enough: put
OR(test1, test2)inside the IF. - Three or more outcomes: nest IF functions, checking the strictest test first.
Worked example
Rule: a mark of 50 or more is a Pass, 70 or more is a Merit, otherwise Fail. Invented marks in B2:B4 are 62, 48 and 50.
Step 1, two outcomes first. In C2 type =IF(B2>=50,"Pass","Fail"). For 62 it shows Pass, for 48 it shows Fail, and for 50 it shows Pass, because >= includes 50.
Step 2, add Merit. Test the strictest condition first:
=IF(B2>=70,"Merit",IF(B2>=50,"Pass","Fail"))
For 62: not 70 or more, but 50 or more, so Pass.
For 48: Fail. For 50: Pass. A mark of 75 would give Merit.
Step 3, combine tests. If the rule also needs attendance of at least 80 in column D, use =IF(AND(B2>=50,D2>=80),"Pass","Fail"). A mark of 62 with attendance of 85 passes. A mark of 62 with attendance of 70 fails.
The mistake to watch for
Mistaken formula:
=IF(B2>50,"Pass","Fail")The rule says “50 or more”, but this formula says “more than 50”. A mark of exactly 50 returns Fail.
The correction is >=. Another common slip is ordering a nested IF with the loosest test first:
=IF(B2>=50,"Pass",IF(B2>=70,"Merit","Fail"))A mark of 75 passes the first test and returns Pass, so Merit is never reached.
Put the strictest test first, and always test values on each boundary.
Check yourself
Try these, then open each answer.
1. Using the Merit formula above, what is returned for 70, 69 and 49?
Show answer
70 is Merit, since it is 70 or more. 69 is Pass. 49 is Fail. Merit, Pass, Fail.
2. A student has a mark of 45 and attendance of 85. The rule is “mark at least 50 OR attendance at least 80”. What does =IF(OR(B2>=50,D2>=80),"Pass","Fail") return?
Show answer
The first test is false and the second is true. OR needs only one, so it returns Pass. With AND instead, it would return Fail.
3. Why does a nested IF test the highest band first?
Show answer
The first true test wins. If a looser test such as 50 or more came first, a mark of 75 would stop there and never be tested for 70 or more.
Where this leads next
When a rule has many bands, a table is neater than many nested IFs. See using a lookup with clearly stated assumptions.
Review the absolute reference lesson if your pass mark sits in a cell. Practise predictions with the spreadsheet reference practice grid.
If you want a teacher to check your logic on rules you write yourself, see online one-to-one ICT tuition.