IF with AND and OR
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which formula gives "Pass" only when BOTH the mark in B2 is above 50 AND C2 says "Yes"?
2. A student gets a free ticket if they are in Year 9 OR in Year 10. Which test is correct?
3. B2 is 40 and C2 is "Yes". What does =IF(OR(B2>50,C2="Yes"),"Pass","Fail") show?
4. B2 is 40 and C2 is "Yes". What does =IF(AND(B2>50,C2="Yes"),"Pass","Fail") show?
5. Which statement about AND inside an IF is true?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=IF(AND(B2>50,C2="Yes"),"Pass","Fail") | A=IF(B2>=80,"High",IF(B2>=50,"Medium","Low")) | |
| 2=IF(OR(B2>=90,C2>=90),"Top","Other") | BAND 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"). | |
| 3Nested IF sorts a mark into three categories: High for 80 or more, Medium for 50 to 79, Low for under 50 | CGives Pass only if B2 is over 50 AND C2 is Yes. If either test fails, it gives Fail. | |
| 4Both conditions must be true: the mark is at least 50 and the attendance is at least 80 | DGives Top if B2 or C2 is 90 or more (or both). Gives Other only if both are under 90. | |
| 5=IF(AND(B2>50,C2="Yes"),"Pass","Fail" | EThe 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. | |
| 6=IF(B2>50 AND C2="Yes","Pass","Fail") | F=IF(AND(B2>=50,C2>=80),"Pass","Fail") |
C
Definitions
=IF(AND(B2>50,C2="Yes"),"Pass","Fail")
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=IF(___(B2>50,C2="Yes"),"Pass","Fail")
2.
=IF(__(B2>90,C2>90),"Top","Other")
3.
=IF(AND(B2_50,C2="Yes"),"Pass","Fail")
4.
=IF(B2>=80,"High",__(B2>=50,"Medium","Low"))
5.
=__(OR(B2="Yes",C2="Yes"),"Go","Stop")
E
Spot the Error
Shade in the letter of the line that contains an 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")
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"
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")
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")
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")
Answer Key
IF with AND and OR
Section A: Quick Questions
Q1
A — =IF(AND(B2>50,C2="Yes"),"Pass","Fail")
Q2
B — =IF(OR(B2=9,B2=10),"Free","Pay")
Q3
A — Pass
Q4
A — Fail
Q5
B — AND is TRUE only when every test inside it is TRUE.
Section B: Match the Pairs
Q1
=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.
Q2
=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.
Q3
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"))
Q4
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")
Q5
=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.
Q6
=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").
Section C: Definitions
Q1
=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.Section D: Fill in the Blanks
Q1
=IF(AND(B2>50,C2="Yes"),"Pass","Fail")Q2
=IF(OR(B2>90,C2>90),"Top","Other")Q3
=IF(AND(B2>50,C2="Yes"),"Pass","Fail")Q4
=IF(B2>=80,"High",IF(B2>=50,"Medium","Low"))Q5
=IF(OR(B2="Yes",C2="Yes"),"Go","Stop")Section E: Spot the Error
Q1
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").
Q2
D — The formula is missing its final closing bracket. Every opening bracket needs a closing one, so it should end with "N").
Q3
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".
Q4
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").
Q5
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").