Flagging out-of-range values with IF and OR
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which formula flags a mark in B2 that is below 0 OR above 100?
2. Why does AND fail when checking for values below 0 and above 100?
3. B2 holds 105. What does =IF(OR(B2<0,B2>100),"Check","OK") show?
4. B2 holds -5. What does =IF(OR(B2<0,B2>100),"Check","OK") show?
5. B2 holds 100. What does =IF(OR(B2<0,B2>100),"Check","OK") show?
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(OR(B2<0,B2>100),"Check","OK") | A=IF(OR(B2<0,B2>100),"Check","OK") | |
| 2=IF(OR(C2<0,C2>100),"Check","OK") | BShows Check if the mark in B2 is below 0 or above 100, otherwise shows OK | |
| 3OR flags a value that fails either the minimum test or the maximum test, so one true test is enough | CAND needs both tests to be true, but no value is below 0 and above 100 at the same time. Every row shows OK. Use OR instead. | |
| 4To list only the problem rows, filter the flag column for the word Check | DFilter column E where the value = "Check" | |
| 5=IF(AND(B2<0,B2>100),"Check","OK") | EThe OR bracket is never closed after B2>100, so Check and OK become extra OR tests. Close OR with ) before the comma. | |
| 6=IF(OR(B2<0,B2>100,"Check","OK") | FFlags an English mark in C2 that is out of range, and shows OK for a valid one |
C
Definitions
=IF(OR(B2<0,B2>100),"Check","OK")
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=IF(__(B2<0,B2>100),"Check","OK")
2.
=IF(OR(B2<0,B2_100),"Check","OK")
3.
=IF(OR(B2_0,B2>100),"Check","OK")
4.
=__(OR(B2<0,B2>100),"Check","OK")
5.
=IF(OR(B2<0,__>100),"Check","OK")
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
=IF(AND(B2<1,B2>5),"Check","")
B
=IF(OR(B3<1,B3>5),"Check","")
C
=IF(OR(B4<1,B4>5),"Check","")
Question 2
A
=IF(OR(B2<1,B2>5),"Check","")
B
=IF(OR(B3<1,B3<5),"Check","")
C
=IF(OR(B4<1,B4>5),"Check","")
Question 3
A
=IF(OR(C2<1,C2>5),Check,"")
B
=IF(OR(C3<1,C3>5),"Check","")
C
=IF(OR(C4<1,C4>5),"Check","")
Question 4
A
=IF(OR(B2<1,B2>5),"Check","")
B
=IF(OR(B3<1,B3>5),"Check","")
C
=IF(OR(B4<1,B4>5),"Check","")
D
=IF(OR(B5<1,B4>5),"Check","")
Question 5
A
=IF(OR(B2<1,B2>5),"Check","")
B
Copy the formula down column C
C
Filter column C for OK
Answer Key
Flagging out-of-range values with IF and OR
Section A: Quick Questions
Q1
B — =IF(OR(B2<0,B2>100),"Check","OK")
Q2
A — No value can be below 0 and above 100 at the same time
Q3
A — Check
Q4
A — Check
Q5
B — OK
Section B: Match the Pairs
Q1
=IF(OR(B2<0,B2>100),"Check","OK") → Shows Check if the mark in B2 is below 0 or above 100, otherwise shows OK
Q2
=IF(OR(C2<0,C2>100),"Check","OK") → Flags an English mark in C2 that is out of range, and shows OK for a valid one
Q3
OR flags a value that fails either the minimum test or the maximum test, so one true test is enough → =IF(OR(B2<0,B2>100),"Check","OK")
Q4
To list only the problem rows, filter the flag column for the word Check → Filter column E where the value = "Check"
Q5
=IF(AND(B2<0,B2>100),"Check","OK") → AND needs both tests to be true, but no value is below 0 and above 100 at the same time. Every row shows OK. Use OR instead.
Q6
=IF(OR(B2<0,B2>100,"Check","OK") → The OR bracket is never closed after B2>100, so Check and OK become extra OR tests. Close OR with ) before the comma.
Section C: Definitions
Q1
=IF(OR(B2<0,B2>100),"Check","OK") — Shows Check if B2 is below 0 or above 100, otherwise OK. OR needs just one true test, so it catches both ends. AND would never work because no value is both below 0 and above 100.Section D: Fill in the Blanks
Q1
=IF(OR(B2<0,B2>100),"Check","OK")Q2
=IF(OR(B2<0,B2>100),"Check","OK")Q3
=IF(OR(B2<0,B2>100),"Check","OK")Q4
=IF(OR(B2<0,B2>100),"Check","OK")Q5
=IF(OR(B2<0,B2>100),"Check","OK")Section E: Spot the Error
Q1
A — AND needs both tests true, but no rating is below 1 and above 5 at once, so nothing is ever flagged. It should be OR.
Q2
B — The second test should be B3>5 to catch values above the maximum. B3<5 would flag most valid ratings.
Q3
A — Text results must be inside quotes. Check should be "Check", otherwise the spreadsheet treats it as a name and shows an error.
Q4
D — Both tests must look at the same cell. The second test should be B5>5, not B4>5, which checks the row above.
Q5
C — To find the out-of-range values you filter for Check. The formula leaves the valid rows blank, so filtering for OK finds nothing.