A short explanation on the use of the following functions: IF, AND, OR, NOT

The IF function

The isolated IF function

"If the note in A1 is greater than or equal to 19, then display the number 20, otherwise display A1" can be expressed as: =IF(A1>=19;20;A1) "If the data in A1 is "Reset", then subtract 10%, otherwise keep A1", can be expressed as: =A1*IF(A1="Reset";1-10%;1)

Nested IF functions

"If the note in A1 is greater than 12, then display "continue"; if A1> 18, then add "that's fine" or display "satisfactory". But if A1 <= 12, then display "to be improved" , can be expressed as
=IF(A1>12;"continue, " & IF(A1>18;"that's fine";"satisfactory");"to be improved") or the using the following formula:
=IF(A1<12;"to be improved";SI(A1<18;"continue, satisfactory";"continue, to be improved"))

If the note in A1 is less than 4, then display "nil"; if A1 is between 4 and 8, then display "not enough"; if A1 is between 8 and 12, then display "mid"; if A1 is between 12 and 16, then display "good", otherwise display "very good", can be expressed as:
=IF(A1<4;"nil";IF(A1<8;"not enough";IF(A1<12;"mid";SI(A1<16;"good";"very good"))))

The AND function

Display "The Countess" if all these conditions are met: A2(sex)=female, B2(status)=married, C2(husband)=count and D2(score)=present, otherwise display "Hello" can be expressed as:
=IF(AND(A2="woman";B2="married";C2="count"; D2="present");"The Countess !";"Hello")

The OR function

Display "Drive" if A3 contains the following data: plane, formula 1, moto, display "Driver" if A3 contains "car" or "work" otherwise display "?", can be expressed as:
=IF(OR(A3="plane";A3="formula 1";A3="moto");"Drive";IF(OR(A3="car";A3="work");"Driver";"?"))

The NOT function

=IF(INT(A3)=A3;"integer";"decimal") can also be written as
=IF(NOT(INT(A3)=A3);"decimal";"integer")

=IF(A4<>"french";"foreigner";"european") can also be written as
=IF(NOT(A4="french");"foreigner";"european")

Combinations

The AND, OR and NOT are commonly used in association with the IF function.

Published by jak58.
Latest update on August 26, 2013 at 06:44 AM by jak58.

This document, titled "The logic functions," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).