Using logic in a spreadsheet
Logical functions test a condition and give TRUE or FALSE: AND(cond1; cond2; ...) is TRUE if all conditions are TRUE, OR(...) is TRUE if at least one is TRUE, and NOT(cond) reverses the value. IF(condition; value1; value2) gives value1 if the condition is TRUE and value2 otherwise; with nested IF more than two cases can be told apart. For example, the larger of two numbers is =IF(A1>B1; A1; B1); if the numbers are equal the condition is FALSE and B1 is taken, which equals A1, so the result is still right. Text functions: LEN(text) gives the length, LEFT(text; n) the first n characters, RIGHT(text; n) the last n, MID(text; start; n) a piece from the middle, and & joins texts. In programs adapted to a language the function names may be written differently (for example, a Russian-language Excel shows a different name for IF) and the argument separator may be “;” or “,” depending on regional settings; text goes in quotation marks. Checking a password with IF in a sheet is not protection: never write real passwords in a spreadsheet.
The PE teacher stated a rule: if it rains, the lesson is in the hall; if not, in the yard. Pupils easily worked out where to go depending on the weather.
Programmers, dispatchers and decision makers write conditional rules.
Write three “if ..., I will ...; otherwise ...” rules from your daily life.
| A | B | C | |
|---|---|---|---|
| 1 | 35 | 48 | 48 |
“Logic workshop”: pairs enter two numbers and write IF formulas giving the larger, the smaller and their difference, and have a classmate test them with chosen numbers.