Practical lesson: logic in a spreadsheet
In a practical lesson we work out a formula’s value ourselves before the computer does, then compare. =IF(A1>100; 100; A1) caps a score at 100: a number above 100 is replaced by 100 and the others stay (the ready MIN(A1; 100) function does the same). Watch the boundary value: for the rule “60 or more – passed”, writing =IF(A1>60; "passed"; "failed") wrongly gives “failed” for A1 = 60; the right formula is =IF(A1>=60; "passed"; "failed"). If we copy a formula to another cell, relative references shift: =A1*2 in B1 becomes =A3*2 when copied to B3. Frequent mistakes: a misspelled function name gives #NAME?, mixing text with numbers gives #VALUE!, dividing by zero gives #DIV/0!, and text without quotation marks also gives #NAME?; a narrow column shows #####. The number of brackets must match and the separator (; or ,) must fit the settings. Test every formula with at least three kinds of values: for example negative, zero and positive, or below the boundary, the boundary itself and above it. Get the program legally: free LibreOffice or Google Sheets exist, so do not use cracked copies.
A pupil worked through their sum line by line on paper: add up, multiply by 3, subtract 4. By writing the result of each step they found at which step the mistake appeared.
Programmers and testers follow code step by step to find a mistake, and accountants recheck calculations.
Choose a number and do three operations on it in turn on paper, such as multiply by 2, subtract 3, divide by 4, writing the result of each step.
| A1 | =IF(A1>=60; "passed"; "failed") |
|---|---|
| 59 | failed |
| 60 | passed |
| 61 | passed |
| A | B | C | |
|---|---|---|---|
| 1 | 6 | 8 | 4 |
“Bug hunters”: the teacher gives 5 faulty formulas (no quotation marks, a missing bracket, a wrong function name...); groups find and fix the errors.