The IF function in a spreadsheet

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

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" if it is not?
A =IF(B2>=50,"Fail","Pass")
B =IF(B2>=50,"Pass","Fail")
2. In =IF(A2>10,"Big","Small"), what shows if A2 is 4?
A Small
B Big
3. Which one writes the text results correctly?
A =IF(C2>=100,"Yes","No")
B =IF(C2>=100,Yes,No)
4. How many parts does an IF function have inside its brackets?
A 3: a test, a value if true and a value if false
B 2: a test and one result
5. You wrote =IF(B2>=50,"Pass","Fail") in C2. You copy it down to C3. What does the formula in C3 test?
A B2>=50
B B3>=50
B

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left columnRight column
1=IF(B2>=50,"Pass","Fail") A=IF(A2="Yes","Going","Away")
2=IF(C2>100,"Over","OK") BThe text results Pass and Fail are missing quotes. They should be "Pass" and "Fail".
3Text results must go inside quotes so the spreadsheet reads them as words CShows Over if C2 is bigger than 100, otherwise shows OK
4Copying the formula down from row 2 changes B2 to B3, B4 and so on, so every row gets its own result DThe formula has no third value for when the test is false. It needs a value_if_false such as "Fail".
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(B2>=50,"Pass","Fail")
4.
C2: =IF(B2>=50,"Pass","Fail") C3: =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 =IF(B2>=50,Pass,"Fail")
B =IF(B3>=50,"Pass","Fail")
C =IF(B4>=50,"Pass","Fail")
Question 2
A =IF(B2>=50,"Pass","Fail")
B =IF(B2>=50,"Pass","Fail")
C =IF(B4>=50,"Pass","Fail")
Question 3
A =IF(B2>=50,"Pass","Fail")
B =IF(B3=>50,"Pass","Fail")
C =IF(B4>=50,"Pass","Fail")
Question 4
A =IF(C2>10,"Yes","No")
B =IF(C3>10,"Yes","No")
C =IF(C4>10,"Yes","No")
D IF(C5>10,"Yes","No")
Question 5
A =IF(B2>=30,"Hot","Mild")
B =IF(B3>=30,"Hot","Mild")
C =IF(B4>=30,"Hot","Mild")
D =IF(B5>=30,"Mild","Hot")

Answer Key

The IF function in a spreadsheet
Section A: Quick Questions
Q1
B — =IF(B2>=50,"Pass","Fail")
Q2
A — Small
Q3
A — =IF(C2>=100,"Yes","No")
Q4
A — 3: a test, a value if true and a value if false
Q5
B — B3>=50
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>100,"Over","OK") → Shows Over if C2 is bigger than 100, otherwise shows OK
Q3
Text results must go inside quotes so the spreadsheet reads them as words → =IF(A2="Yes","Going","Away")
Q4
Copying the formula down from row 2 changes B2 to B3, B4 and so on, so every row gets its own result → =IF(B3>=50,"Pass","Fail")
Q5
=IF(B2>=50,Pass,Fail) → The text results Pass and Fail are missing quotes. They should be "Pass" and "Fail".
Q6
=IF(B2>=50,"Pass") → The formula has no third value for when the test is false. It needs a value_if_false such as "Fail".
Section C: Definitions
Q1
=IF(test, value_if_true, value_if_false) — Checks a test, like B2>=50. Shows the second value if the test is true and the third if it is false. Text results go in quotes, e.g. =IF(B2>=50,"Pass","Fail").
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
C2: =IF(B2>=50,"Pass","Fail") C3: =IF(B3>=50,"Pass","Fail")
Q5
=IF(B2<50,"Fail","Pass")
Section E: Spot the Error
Q1
A — Text results must be in quotes. Pass should be "Pass", otherwise the spreadsheet thinks it is a name and shows an error.
Q2
B — When the formula is copied down, the row number changes. This row should check B3, not B2, so it matches its own row.
Q3
B — The comparison operator is written the wrong way round. 'Greater than or equal to' is >=, not =>.
Q4
D — A formula must start with an equals sign. It should be =IF(C5>10,"Yes","No"), or the cell just shows the text.
Q5
D — The results are in the wrong order. The second value shows when the test is true, so a temperature of 30 or more should give "Hot".