Logical Functions in Microsoft Excel

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.

Logical Functions 1

The various options present under this heading are:

Logical Functions 2

Mrs. Roy has the following data about her students.

Logical Functions 3


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 4


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 5


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

Logical Functions 6

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 8


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)

Logical Functions 9

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,……..)

Leave a Comment

Your email address will not be published. Required fields are marked *