The IF function in a spreadsheet
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which formula shows "Pass" when the mark in B2 is 50 or more, and "Fail" otherwise?
2. What does =IF(B2>=50,"Pass","Fail") show when B2 contains 50?
3. In =IF(B2>=50,"Pass","Fail"), which part is the test?
4. What does =IF(B2>=50,"Pass","Fail") show when B2 contains 49?
5. You typed =IF(B2>=50,"Pass","Fail") in C2. What happens when you copy it down to C3?
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(B2>=50,"Pass","Fail") | AThe value_if_false argument is missing. The function needs a test, a true result and a false result, such as "Fail". | |
| 2=IF(C2<18,"Minor","Adult") | B=IF(B2>=50,"Pass","Fail") | |
| 3Text results must be put inside quotation marks so the spreadsheet shows them as words | CThe text results Pass and Fail are missing quotation marks. They need to be written as "Pass" and "Fail". | |
| 4Copy the formula down a column so each row tests its own cell (B3, B4 and so on) | DShows Minor if the age in C2 is under 18, otherwise shows Adult | |
| 5=IF(B2>=50,Pass,Fail) | EShows Pass if the mark in B2 is 50 or more, otherwise shows Fail | |
| 6=IF(B2>=50,"Pass") | F=IF(B3>=50,"Pass","Fail") |
C
Definitions
=IF(test, value_if_true, value_if_false)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=__(B2>=50,"Pass","Fail")
2.
=IF(B2__50,"Pass","Fail")
3.
=IF(__>=50,"Pass","Fail")
4.
=IF(__>=50,"Pass","Fail")
5.
=IF(B2_50,"Fail","Pass")
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
C1: Result
B
C2: =IF(B2>=50,Pass,"Fail")
C
C3: =IF(B3>=50,"Pass","Fail")
Question 2
A
C1: Result
B
C2: =IF(B2>=50,"Pass","Fail")
C
C3: =IF(B2>=50,"Pass","Fail")
Question 3
A
B2: 62
B
C2: IF(B2>=50,"Pass","Fail")
C
C2 shows Pass
Question 4
A
B2: 92
B
C2: =IF(B2=>90,"Great","Low")
C
C2 shows Great
Question 5
A
=IF(B2>=50 "Pass","Fail")
B
Copy it down column C
C
C3 uses B3 in its test
Answer Key
The IF function in a spreadsheet
Section A: Quick Questions
Q1
A — =IF(B2>=50,"Pass","Fail")
Q2
B — Pass
Q3
B — B2>=50
Q4
A — Fail
Q5
A — It becomes =IF(B3>=50,"Pass","Fail") and checks the mark in B3
Section B: Match the Pairs
Q1
=IF(B2>=50,"Pass","Fail") → Shows Pass if the mark in B2 is 50 or more, otherwise shows Fail
Q2
=IF(C2<18,"Minor","Adult") → Shows Minor if the age in C2 is under 18, otherwise shows Adult
Q3
Text results must be put inside quotation marks so the spreadsheet shows them as words → =IF(B2>=50,"Pass","Fail")
Q4
Copy the formula down a column so each row tests its own cell (B3, B4 and so on) → =IF(B3>=50,"Pass","Fail")
Q5
=IF(B2>=50,Pass,Fail) → The text results Pass and Fail are missing quotation marks. They need to be written as "Pass" and "Fail".
Q6
=IF(B2>=50,"Pass") → The value_if_false argument is missing. The function needs a test, a true result and a false result, such as "Fail".
Section C: Definitions
Q1
=IF(test, value_if_true, value_if_false) — Checks a condition, like B2>=50, and shows one result if it is true and another if it is false. Text results go in quotes, and you can copy it down a column.Section D: Fill in the Blanks
Q1
=IF(B2>=50,"Pass","Fail")Q2
=IF(B2>=50,"Pass","Fail")Q3
=IF(B2>=50,"Pass","Fail")Q4
=IF(B3>=50,"Pass","Fail")Q5
=IF(B2<50,"Fail","Pass")Section E: Spot the Error
Q1
B — Text results must be inside quotes. Without quotes, Excel thinks Pass is a name and shows an error. It should be "Pass".
Q2
C — When the formula is copied down to row 3, the test should check B3. Here it still checks B2, so row 3 would show the wrong result. It should be B3>=50.
Q3
B — A formula must start with an equals sign. Without it, the cell just shows the text. It should be =IF(B2>=50,"Pass","Fail").
Q4
B — The 'greater than or equal to' operator is >=, not =>. The test should be B2>=90.
Q5
A — Each part of IF must be separated by a comma. A comma is missing after the test B2>=50.