← ACARA Digital Technologies Years 9–10

IF with AND and OR

AND or OR inside IF joins several tests into one TRUE/FALSE. AND needs every test true. OR needs only one true. The IF then gives its first result if TRUE, otherwise its second.

Part of: ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 23

Understanding IF with AND and OR in ACARA Digital Technologies Years 9–10

What it does: The second IF sits in the false part of the first IF, so each mark is tested in turn and lands in one of three categories. AND joins the two tests into one TRUE/FALSE answer that is TRUE only when both tests are true, so the IF returns Pass only then. B2>50 is FALSE. AND needs every test to be TRUE, so the whole test is FALSE and the IF gives its second result, "Fail". In a spreadsheet, AND is a function. Its tests go inside brackets, separated by commas: AND(B2>50,C2="Yes"). AND(...) is closed correctly, but the IF( has no closing bracket. Adding ) after "Fail" fixes the formula.

Common mistakes: The tests inside AND must be separated by a comma. B3>50 and C3>5 need a comma between them: =IF(AND(B3>50,C3>5),"Y","N"). The formula is missing its final closing bracket. Every opening bracket needs a closing one, so it should end with "N").

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add IF with AND and OR to a class →

This module's key terms are also in a printable crossword (Modules 333–377) and word search (Modules 333–412).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which formula gives "Pass" only when BOTH the mark in B2 is above 50 AND C2 says "Yes"?
=IF(AND(B2>50,C2="Yes"),"Pass","Fail")=IF(OR(B2>50,C2="Yes"),"Pass","Fail")
Matches
=IF(AND(B2>50,C2="Yes"),"Pass","Fail"
↓
The closing bracket for the IF is missing. Every opening bracket needs a matching closing one, so the formula should end with two brackets after "Fail" is not needed, just one ) to close the IF.
Applying
Meanings
Term
=IF(AND(B2>50,C2="Yes"),"Pass","Fail")
Meaning
AND or OR inside IF joins several tests into one TRUE/FALSE. AND needs every test true. OR needs only one true. The IF then gives its first result if TRUE, otherwise its second.
Practice
=IF(AND(B2>=50,C2>=50,D2>=50),"Pass","Fail")
Testing
Gaps
=IF((B2>50,C2="Yes"),"Pass","Fail")
Check Answer
Spots
1=IF(AND(B2>50,C2>5),"Y","N")
2=IF(AND(B3>50 C3>5),"Y","N")

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Giraffe' badge — High Perspective, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which formula gives "Pass" only when BOTH the mark in B2 is above 50 AND C2 says "Yes"?
A. =IF(AND(B2>50,C2="Yes"),"Pass","Fail") ✓
B. =IF(OR(B2>50,C2="Yes"),"Pass","Fail")
AND needs every test to be TRUE. OR would pass a student who met only one of the tests.
2. A student gets a free ticket if they are in Year 9 OR in Year 10. Which test is correct?
A. =IF(AND(B2=9,B2=10),"Free","Pay")
B. =IF(OR(B2=9,B2=10),"Free","Pay") ✓
A student can't be in Year 9 and Year 10 at once, so AND would always be FALSE. OR only needs one test to be TRUE.
3. B2 is 40 and C2 is "Yes". What does =IF(OR(B2>50,C2="Yes"),"Pass","Fail") show?
A. Pass ✓
B. Fail
B2>50 is FALSE but C2="Yes" is TRUE. OR needs only one TRUE test, so the IF gives its first result, "Pass".
4. B2 is 40 and C2 is "Yes". What does =IF(AND(B2>50,C2="Yes"),"Pass","Fail") show?
A. Fail ✓
B. Pass
B2>50 is FALSE. AND needs every test to be TRUE, so the whole test is FALSE and the IF gives its second result, "Fail".
5. Which statement about AND inside an IF is true?
A. AND is TRUE when at least one test inside it is TRUE.
B. AND is TRUE only when every test inside it is TRUE. ✓
That second description is how OR works. AND is stricter and needs all of its tests to be TRUE.

Match the Pairs

=IF(AND(B2>50,C2="Yes"),"Pass","Fail") → Gives Pass only if B2 is over 50 AND C2 is Yes. If either test fails, it gives Fail.
=IF(OR(B2>=90,C2>=90),"Top","Other") → Gives Top if B2 or C2 is 90 or more (or both). Gives Other only if both are under 90.
Nested IF sorts a mark into three categories: High for 80 or more, Medium for 50 to 79, Low for under 50 → =IF(B2>=80,"High",IF(B2>=50,"Medium","Low"))
Both conditions must be true: the mark is at least 50 and the attendance is at least 80 → =IF(AND(B2>=50,C2>=80),"Pass","Fail")
=IF(AND(B2>50,C2="Yes"),"Pass","Fail" → The closing bracket for the IF is missing. Every opening bracket needs a matching closing one, so the formula should end with two brackets after "Fail" is not needed, just one ) to close the IF.
=IF(B2>50 AND C2="Yes","Pass","Fail") → AND is written like plain English inside the test. In a spreadsheet, AND is a function and must be written as AND(B2>50,C2="Yes").

Key Term

=IF(AND(B2>50,C2="Yes"),"Pass","Fail")
AND or OR inside IF joins several tests into one TRUE/FALSE. AND needs every test true. OR needs only one true. The IF then gives its first result if TRUE, otherwise its second.

Fill in the Blanks

1. =IF(AND(B2>50,C2="Yes"),"Pass","Fail")
Hint: Type the function that is only TRUE when every test inside it is TRUE.
2. =IF(OR(B2>90,C2>90),"Top","Other")
Hint: Type the function that is TRUE when at least one of its tests is TRUE.
3. =IF(AND(B2>50,C2="Yes"),"Pass","Fail")
Hint: Type the comparison operator that tests whether B2 is more than 50.
4. =IF(B2>=80,"High",IF(B2>=50,"Medium","Low"))
Hint: Type the function that is nested in the false position to create a third category.
5. =IF(OR(B2="Yes",C2="Yes"),"Go","Stop")
Hint: Type the function that picks one of two results depending on the combined test.

Spot the Error

Question 1
A =IF(AND(B2>50,C2>5),"Y","N")
B =IF(AND(B3>50 C3>5),"Y","N")
C =IF(AND(B4>50,C4>5),"Y","N")
Line B — The tests inside AND must be separated by a comma. B3>50 and C3>5 need a comma between them: =IF(AND(B3>50,C3>5),"Y","N").
Question 2
A =IF(OR(B2>50,C2>5),"Y","N")
B =IF(OR(B3>50,C3>5),"Y","N")
C =IF(OR(B4>50,C4>5),"Y","N")
D =IF(OR(B5>50,C5>5),"Y","N"
Line D — The formula is missing its final closing bracket. Every opening bracket needs a closing one, so it should end with "N").
Question 3
A =IF(AND(B2>50,C2="Y"),"P","F")
B =IF(AND(B3>50,C3="Y"),"P","F")
C =IF(AND(B4>50,C4=Y),"P","F")
Line C — Text in a test must be in double quotes. Y without quotes is read as a name, which causes an error. It should be C4="Y".
Question 4
A =IF(AND(B2>50,C2>50),"Y","N")
B =IF(AND(B3>50,C3>50),"Y"."N")
C =IF(AND(B4>50,C4>50),"Y","N")
Line B — The results inside IF must be separated by commas. A full stop between "Y" and "N" is wrong. It should be =IF(AND(B3>50,C3>50),"Y","N").
Question 5
A =IF(OR(B2>50,C2>50),"Y","N")
B =IF(OR(B3>50,C3>50),"Y","N")
C =IF(OR(B4>50,C4>50),"Y","N"))
D =IF(OR(B5>50,C5>50),"Y","N")
Line C — There is one bracket too many at the end. IF and OR each open one bracket, so the formula needs exactly two closing brackets after "N": ...,"N").

Student with a class code? Join your class →