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.
Options
IF functions
Why
2
1
one test, two results
3
2
the second IF sits in the false part of the first
4
3
one more IF for each extra option
n
n − 1
the last option needs no test
Worked examples
B2fx=IF(A2<=1,4,IF(A2<=5,7,12))
A
B
1
Parcel (kg)
Postage
2
0.5
4
3
1
4
4
3
7
5
5
7
6
5.5
12
7
8
12
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.
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 price
Price from the wrong formula
0.5
4
7
1
4
7
3
7
7
5
7
7
5.5
12
12
8
12
12
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?
=IF(B2<20,"Calm",IF(B2<50,"Breezy","Stormy")). For 20 the first test is false and the second is true, so Breezy. For 49.9 it is Breezy. For 50 both tests are false, so Stormy.
2
The formula in B2 is shown in the formula bar and is filled down. Work out the four missing results.
100 pages: the first test A2>=100 is true, so Long. 99 pages: the first test is false and 99>=40 is true, so Medium. 40 pages: Medium. 39 pages: both tests are false, so Short.
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"))
A
B
1
Walk (km)
Length
2
12
Medium
3
7
Medium
4
3
Short
The test A2>=5 comes first, so every walk of 5 km or more gets Medium, and the test for Long is never reached. The 12 km walk shows Medium. Put the bigger limit first: =IF(A2>=10,"Long",IF(A2>=5,"Medium","Short")).
4
A formula must choose between six colour names for six score bands. How many IF functions does it need? Explain.
It needs 5 IF functions. For n options you need n − 1 IFs, because the last option is what is left when the other five tests are false.