AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Contents · Computing

Logic in a spreadsheet: practical lesson

Lessons 32 · 1 lessons · B. Boltayev, A. Azamatov, A. Asqarov, M. Sodiqov, G. Azamatova. Fundamentals of Informatics and Computer Technology, Grade 8. Second edition. “O‘zbekiston milliy ensiklopediyasi” State Scientific Publishing House, Tashkent, 2015
32

Practical lesson: logic in a spreadsheet

Textbook: p. 134
GoalPredicts the results of IF formulas, finds and fixes mistakes and understands the effect of relative references when copying.
New words
error code: a message in a cell such as #NAME?, #VALUE! or #DIV/0! · xato kodirelative reference: an address that changes when the formula is copied · nisbiy murojaatquotation marks: the marks that enclose text in a formula · qo‘shtirnoqtesting: trying a formula with several different inputs · tekshirish
Explanation

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.

💡 Where will I use this?
In real life

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.

Who uses it

Programmers and testers follow code step by step to find a mistake, and accountants recheck calculations.

Try it today

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.

Worked examples
A1=IF(A1>=60; "passed"; "failed")
59failed
60passed
61passed
For A1 = 120, 100, 85 the result of =IF(A1>100; 100; A1) is 100, 100, 85. For A1 = 59, 60, 61 the result of =IF(A1>=60; "passed"; "failed") is “failed”, “passed”, “passed”.
C1fx=B1*B1-10*A1
ABC
1684
A1 = 6, B1 = A1/2+5 → 8, C1 = B1*B1 – 10*A1 → 64 – 60 = 4. =IF(B1>C1; "B1"; "C1") gives “B1” because 8 > 4 is TRUE. If the C1 formula is copied to C4 it becomes =B4*B4-10*A4 and gives 0 with empty cells.
Class activity

“Bug hunters”: the teacher gives 5 faulty formulas (no quotation marks, a missing bracket, a wrong function name...); groups find and fix the errors.

Practice
1
What does =IF(A1>100; 100; A1) give when A1 = 135?
2
If A1 = 6, B1 = A1/2+5 and C1 = B1*B1 – 10*A1, what is C1?
3
What does MID("informatika"; 3; 4) give?
4
For the rule “60 or more – passed”, why is =IF(A1>60; "passed"; "failed") wrong, and how can it be fixed?
Story of the Week · The Khwarizmi WayA fertiliser test for seedlingsClose the week: all of this week’s subjects in one storyRead →