Short, medium or long?
A reading club lists the pages in each book. The leader wants a label next to every book: short, medium or long. Another column should say who may borrow.
Typing 200 labels is slow. A decision written once can fill the whole column.
Key words
| Word | What it means |
|---|---|
| Condition | a test that is true or false |
| IF | picks one answer when a condition is true and another when it is false |
| AND | true only when both conditions are true |
| OR | true when at least one condition is true |
| NOT | turns true into false and false into true |
| Nested IF | an IF placed inside another IF, to choose between three or more answers |
| Formula | a calculation typed into a cell, starting with = |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can use IF, AND, OR and NOT, alone and together, to label values and to test them.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
No spreadsheet here
Important
This platform has no spreadsheet program. Here the cells are a table and the formula is shown as text. You do the same job with code. On site you use a real spreadsheet program.
The reading club as cells
| Row | A name | B year | C pages | D fines (pence) |
|---|---|---|---|---|
| 1 | Amy | 8 | 90 | 0 |
| 2 | Bo | 7 | 250 | 50 |
| 3 | Cy | 9 | 400 | 0 |
| 4 | Di | 6 | 120 | 0 |
The cells students fill in are left blank in this preview.
Four decisions, as formulas
| Formula | Says | Answer for row 2 (Bo) |
|---|---|---|
| `=IF(C2<100,"short","not short")` | Label a book by one test | not short |
| `=IF(AND(B2>=7,D2=0),"yes","no")` | Both tests must pass | no (Bo has a fine) |
| `=IF(OR(C2<100,C2>300),"odd","normal")` | Either test may pass | normal |
| `=IF(NOT(D2=0),"owes","clear")` | Turn a test round | owes |
The cells students fill in are left blank in this preview.
Try it: the same decision in SQL
SELECT name, pages,
CASE WHEN pages < 100 THEN 'short'
WHEN pages < 300 THEN 'medium'
ELSE 'long' END AS band
FROM readers;
-- AND, OR and NOT work inside WHERE.
SELECT name FROM readers WHERE year >= 7 AND fines_pence = 0;
Three answers need a nested IF
One IF gives two answers. For three, put a second IF in the "false" part: =IF(C2<100,"short",IF(C2<300,"medium","long")).
It reads top down. The first true test wins, so put the tests in the right order.