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

Using logic in a spreadsheet

Lessons 31 · 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
31

Using logic in a spreadsheet

Textbook: pp. 128–133
GoalSolves logic tasks in a spreadsheet using AND, OR, NOT, IF and text functions.
New words
logical function: a function that tests conditions and gives TRUE or FALSE · mantiqiy funksiyaIF(condition; value1; value2): gives value1 if the condition is true, otherwise value2 · IFnested IF: an IF inside another IF to tell apart more than two cases · ichma-ich IFtext function: LEN, LEFT, RIGHT, MID and joining with & · matn funksiyasi
Explanation

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.

💡 Where will I use this?
In real life

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.

Who uses it

Programmers, dispatchers and decision makers write conditional rules.

Try it today

Write three “if ..., I will ...; otherwise ...” rules from your daily life.

Worked examples
C1fx=IF(A1>B1; A1; B1)
ABC
1354848
Larger and smaller: =IF(A1>B1; A1; B1) gives 48 for 35 and 48, and 12 for 12 and 12. =IF(A1<B1; A1; B1) finds the smaller one. Text: LEN("informatika") = 11, LEFT("informatika"; 4) = "info", MID("informatika"; 3; 4) = "form".
StartA1A1 ≥ 90yesno5A1 ≥ 70yesno4A1 ≥ 50yesno32End
Score to grade: =IF(A1>=90; 5; IF(A1>=70; 4; IF(A1>=50; 3; 2))). At A1 = 77 the first condition is FALSE, the second TRUE, so the result is 4. A wrong hour: =IF(OR(A1<0; A1>23); "error"; "") – with A1 = 25 it shows “error”; it can also be written through negation: =IF(AND(A1>=0; A1<=23); ""; "error").
Class activity

“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.

Practice
1
What is AND(3>1; 2+2=4)?
2
What does =IF(MOD(A1; 2)=0; A1/2; A1*3+1) give when A1 = 7?
3
What does =IF(A1>B1; A1; B1) give when A1 = 7 and B1 = 7?
4
Why does the IF formula for the larger number not fail even when the numbers are equal?
Story of the Week · The Khwarizmi WayA booklet for our grandparentsClose the week: all of this week’s subjects in one storyRead →