Microsoft Excel offers a variety of functions in its ‘Function Library’. In this module, we will look at the logical functions under the heading ‘Logical’ and their uses.
The ‘Logical’ group of functions is present in the ‘Formula’ menu in the ‘Function Library’ towards the left hand side.
The various options present under this heading are:
Mrs. Roy has the following data about her students.
Logical Function – AND
This function is used when the result requires two logical options to be valid. For the data above, a student is considered as ‘Passed’ only if he/she has scored above 50% in both SA1 and SA2. In such a situation, she can use the following formula using the AND function to find this.
Syntax: AND(logical_test_1, logical_test_2,…)
= AND(B2>50%,C2>50%)
Logical Functions- IF, TRUE and FALSE
IF function is used when the result requires one logical option to be valid.
FALSE function is used with other functions to generate the output ‘FALSE’.
TRUE function is used with other functions to generate the output ‘TRUE’.
The syntax for IF is
IF(logical_test,value_if_true, value_if_false)
Supposing in the above case, she decides that she will meet the ones who have scored lesser in SA2 than SA1, she will have a meeting with them, then she can use the formula
=IF(C2<B2,TRUE(),FALSE())
The result will appear as follows.
Logical Functions- IFERROR and IFNA:
This function is used when the value of a certain cell is supposed to be equal to something but is found to be not.
Syntax:
IFERROR(value,value_if_error)
As Mrs. Roy was going through the answer sheets, she realized that there are some remarks by the invigilator on the answer paper of roll number 154336 about malpractice. To mark this on the Excel Sheet, she can use the following formula
=IFERROR(A2=154336,FALSE())
The result appears as follows
IFNA function is used when you want to display a particular value for operations that are not valid, instead of N/A.
Syntax:
Using this function for the above situation,
=IFNA(Value,value_if_na)
Logical Function- NOT
This function can be used to change a ‘FALSE’ function to ‘TRUE’ and vice versa.
Syntax: =NOT(logical)
For example, in the above formula, if she wants to change the F row to the other function, she can use the formula
=NOT(A2=154336)
The result will appear as follows
Logical Functions- OR and XOR
This can be used when we want either of the conditions to be true.
Syntax: OR(logical_test_1,logical_test_2,……)
For example, if Mrs. Roy decides that she will meet the students who have either failed the exams or scored lesser in the second one, she can use the following formula
=OR(B2<50%,C2<50%,C2<B2)
XOR function is used when the user wants either one of the conditions to be true. and the other conditions to be false
Syntax:
XOR(Logical_test_1,logical_test_2,……..)