AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Models and choices

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>=25the 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")
ABC
1PupilMetresResult
2Ravi10Keep practising
3Mia25Badge
4Omar40Badge
5Lola24Keep practising
6Kenji31Badge
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.
CellFormula in the cellShows
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)
AB
1ItemValue
2Roof area (m²)40
3Rain (mm)25
4Tank size (litres)800
5Water 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.
ABC
1ReaderMinutesResult
2Ana45?
3Yuki30?
4Amir29?
5Chen0?
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"
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")
ABC
1ItemNumberSize
2A30?
3B20?
4C25?
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.
ABC
1OrderStickersCost
2x4?
3y10?
4z12?