Week 15 · 2.1 Spreadsheets and analysing data · 3 lessons
2
IF makes a choice
Computing 9: Topic 2.1, pp. 168–197
GoalWrite a simple IF formula with a test and two results, and fill it down a column so that every row gets its own answer.
New words
function · a ready-made tool in a spreadsheet, such as IF or SUM, that is written inside a formulaparameter · an item of information that is given to a function, written inside its brackets and separated by commaslogical test · a question that has the answer True or False, such as B2>=25fill · to copy a formula into the cells next to it by dragging, so that the cell references move with it
Explanation
A formula is a calculation that starts with an equals sign. A function is a ready-made tool that can be used inside a formula. So a function always sits inside a formula, while a formula such as =B2*3 uses no function at all. The IF function chooses between two results. It has three parameters, separated by commas: a logical test, the result if the test is true, and the result if it is false. Text results go in straight double quotation marks. Brackets must close properly. When you fill a formula down a column, each row gets its own copy, and the cell references move down by one row each time. So =IF(B2>=25,…) in C2 becomes =IF(B3>=25,…) in C3. A spreadsheet can show the formulas instead of the answers when you want to check them.
Part of =IF(B2>=25,"Badge","Keep practising")
What it is
B2>=25
the logical test
"Badge"
the result if the test is true
"Keep practising"
the result if the test is false
Worked examples
C2fx=IF(B2>=25,"Badge","Keep practising")
A
B
C
1
Pupil
Metres
Result
2
Ravi
10
Keep practising
3
Mia
25
Badge
4
Omar
40
Badge
5
Lola
24
Keep practising
6
Kenji
31
Badge
A swimming badge. A pupil gets a badge for swimming 25 metres or more. In C2 the formula is =IF(B2>=25,"Badge","Keep practising"). It was typed once and filled down to C6. Mia swam exactly 25, so the test B3>=25 is true. Lola swam 24, so hers is false.
Cell
Formula in the cell
Shows
C2
=IF(B2>=25,"Badge","Keep practising")
Keep practising
C3
=IF(B3>=25,"Badge","Keep practising")
Badge
C4
=IF(B4>=25,"Badge","Keep practising")
Badge
What filling does to the formula. Each cell holds its own copy, with the row number changed. Only the references move. The text and the number 25 stay the same. The last column shows what each cell displays.
B5fx=IF(B2*B3>B4,B4,B2*B3)
A
B
1
Item
Value
2
Roof area (m²)
40
3
Rain (mm)
25
4
Tank size (litres)
800
5
Water kept (litres)
800
Repairing the tank model with IF. The test asks whether the water collected, B2*B3, is more than the tank size in B4. If it is, the result is the tank size, and if not, it is B2*B3. The roof is 40 m², the rain is 25 mm and the tank holds 800 litres. The test 1000>800 is true, so the answer is 800. With 10 mm the answer would be 400.
Class activity
IF cards (no computer). Write five numbers on paper squares and lay them in a column. Make a card with an IF formula, for example =IF(A1>=10,"High","Low"). Slide the card down the column, one square at a time, and each time rewrite the formula with the new row number, work out the result and write it beside the number. Then change 10 to 12 on your card and see which results change. Which numbers are the ones to test with?
Practice
1
Minutes of reading are in B2:B5. Write the formula for C2 that shows Done for 30 minutes or more and More for less, and fill in the four results.
A
B
C
1
Reader
Minutes
Result
2
Ana
45
?
3
Yuki
30
?
4
Amir
29
?
5
Chen
0
?
=IF(B2>=30,"Done","More"). Ana 45 shows Done. Yuki 30 shows Done. Amir 29 shows More. Chen 0 shows More. Yuki’s cell is the boundary.
2
Each of these formulas has one fault. Find it. (a) =IF(B2>=30,Done,More) (b) =IF(B2>=30,"Done","More" (c) IF(B2>=30,"Done","More") (d) =IF B2>=30,"Done","More"
(a) The words Done and More need double quotation marks. (b) The closing bracket is missing. (c) The equals sign at the start is missing, so it would be read as text. (d) The brackets around the parameters are missing.
3
The formula in C2 is =IF(B2*2>50,"Big","Small"). It is filled down to C4. Write the formula in C4 and work out all three results.
C2fx=IF(B2*2>50,"Big","Small")
A
B
C
1
Item
Number
Size
2
A
30
?
3
B
20
?
4
C
25
?
C4 holds =IF(B4*2>50,"Big","Small"). C2: 30 × 2 = 60 is more than 50, so Big. C3: 20 × 2 = 40, so Small. C4: 25 × 2 = 50, which is not more than 50, so Small.
4
Stickers cost 3 each, but if 10 or more are bought they cost 2 each. Write a formula for C2 that gives the cost of the number of stickers in B2, and work out the cost for 4, 10 and 12.
A
B
C
1
Order
Stickers
Cost
2
x
4
?
3
y
10
?
4
z
12
?
=IF(B2>=10,B2*2,B2*3). For 4 stickers the cost is 12. For 10 it is 20. For 12 it is 24. The test must use >= so that exactly 10 gets the lower price.