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
3

Choosing from three or more

Computing 9: Topic 2.1, pp. 168–197
GoalWrite a nested IF formula for three or more options, in the right order, and test it at its boundary values.
New words
nested IF · an IF placed inside the false part of another IF, so that a formula can choose between more than two resultsoption · one of the results a formula may giveboundary value · a value exactly at the edge of a rule, such as 5 when a test says < 5
Explanation

A simple IF has only two results. To choose between three results, put a second IF in the place where the first IF’s false result would go. The second IF runs only when the first test is false. For n options you need n − 1 IF functions, because the last option needs no test: it is what remains when all the tests are false. The order of the tests matters. The first true test wins, so with < or <= tests the smallest limit goes first, and with > or >= tests the largest limit goes first. Always test the values on the edges, such as exactly 5 and exactly 20, because a wrong < or <= shows up there. Count the brackets: every IF needs its own closing bracket at the end.

OptionsIF functionsWhy
21one test, two results
32the second IF sits in the false part of the first
43one more IF for each extra option
nn − 1the last option needs no test
Worked examples
B2fx=IF(A2<=1,4,IF(A2<=5,7,12))
AB
1Parcel (kg)Postage
20.54
314
437
557
65.512
7812
Postage for parcels. A parcel up to 1 kg costs 4, a parcel over 1 kg and up to 5 kg costs 7, and anything heavier costs 12. The formula is =IF(A2<=1,4,IF(A2<=5,7,12)). It has three options and two IFs. The weights 1 and 5 are on the edges, and both belong to the lower price.
Startread rain (mm)rain = 0?yesnoDryrain < 5?yesnoLightrain < 20?yesnoModerateHeavyEnd
Four options need three IFs. A rain gauge in mm is turned into a word: Dry for 0, Light below 5, Moderate below 20, and otherwise Heavy. The flowchart is the same chain of questions as the formula =IF(B2=0,"Dry",IF(B2<5,"Light",IF(B2<20,"Moderate","Heavy"))). The reading 5 gives Moderate, and 20 gives Heavy.
Weight (kg)Correct pricePrice from the wrong formula
0.547
147
377
577
5.51212
81212
The order matters. A learner wrote =IF(A2<=5,7,IF(A2<=1,4,12)) for the parcels, with the larger limit first. The second test can never be reached for a weight of 5 or less, so the 4 never appears. The weights 0.5 and 1 are given 7, and the correct price is 4. The wrong rows are highlighted.
Class activity

Living nested IF (no computer). Mark a line on the floor with chalk or tape and put three cards along it with the labels Low, Mid and High, with the limits 5 and 15 written on the floor. A partner gives you a number and you walk to the right card, asking only one question at a time: ‘is it less than 5?’ and then ‘is it less than 15?’ Count how many questions each number needs. Try 4, 5, 14, 15 and 20. Which numbers needed only one question?

Practice
1
Write a nested IF formula for cell C2 that reads a wind speed in B2: Calm if it is less than 20, Breezy if it is less than 50, and Stormy otherwise. What is shown for 20, for 49.9 and for 50?
2
The formula in B2 is shown in the formula bar and is filled down. Work out the four missing results.
B2fx=IF(A2>=100,"Long",IF(A2>=40,"Medium","Short"))
AB
1Book (pages)Length
2100?
399?
440?
539?
3
This formula should give Long for a walk of 10 km or more, Medium for 5 to 9.9 km and Short below 5 km. Find the fault, and write the repaired formula.
B2fx=IF(A2>=5,"Medium",IF(A2>=10,"Long","Short"))
AB
1Walk (km)Length
212Medium
37Medium
43Short
4
A formula must choose between six colour names for six score bands. How many IF functions does it need? Explain.