In this guide, you will learn how to work with logical functions in Microsoft Excel 2019. There are various logical functions which are present in the Excel 2019 version, as mentioned below:
Use the TRUE
and FALSE
functions when you want to showcase if a given condition is met or not. For instance, 5 < 3
is a correct condition, and when a TRUE
function is used with an IF
function (or any other conditional function) it returns a TRUE
value. Had the condition been wrong, it would have resulted in the FALSE
value.
Notice that the TRUE
function is not the same as the TRUE
value. The TRUE
function doesn't hold any argument inside the round brackets.
A logical OR
function follows the given truth table:
Input A | Input B | Input C | Output |
---|---|---|---|
0 | 0 | 0 | 0 |
0 | 0 | 1 | 1 |
0 | 1 | 0 | 1 |
0 | 1 | 1 | 1 |
1 | 0 | 0 | 1 |
1 | 0 | 1 | 1 |
1 | 1 | 0 | 1 |
1 | 1 | 1 | 1 |
In the above table, you can also consider 0
as a FALSE
value and 1
as a TRUE
value. As you can observe if any input has a TRUE
value then the output of the logical OR
function is TRUE
. However, if all the inputs are FALSE
then the output becomes FALSE
.
Here's a scenario that will help you implement the logical OR
function in Excel. You take five students' attendance who have registered for a workshop. The final attendance is marked only when a student is present on any one of the days. Here's the data:
A | B | C | D | E | |
---|---|---|---|---|---|
Student list | Day 1 | Day 2 | Day 3 | Attendance | |
1 | Student 1 | TRUE | FALSE | TRUE | ? |
2 | Student 2 | TRUE | TRUE | TRUE | ? |
3 | Student 3 | TRUE | FALSE | TRUE | ? |
4 | Student 4 | TRUE | TRUE | FALSE | ? |
5 | Student 5 | FALSE | FALSE | FALSE | ? |
So, to fill the attendance, you use the logical OR
formula with this syntax:
1=OR(logical_value1, logical_value2, ... )
Put the formula =OR(B1, C1, D1)
in cell E1
and then apply it in subsequent rows. This will result in the updated attendance as shown:
A | B | C | D | E | |
---|---|---|---|---|---|
Student list | Day 1 | Day 2 | Day 3 | Attendance | |
1 | Student 1 | TRUE | FALSE | TRUE | TRUE |
2 | Student 2 | TRUE | TRUE | TRUE | TRUE |
3 | Student 3 | TRUE | FALSE | TRUE | TRUE |
4 | Student 4 | TRUE | TRUE | FALSE | TRUE |
5 | Student 5 | FALSE | FALSE | FALSE | FALSE |
A logical AND
function follows the given truth table:
Input A | Input B | Input C | Output |
---|---|---|---|
0 | 0 | 0 | 0 |
0 | 0 | 1 | 0 |
0 | 1 | 0 | 0 |
0 | 1 | 1 | 0 |
1 | 0 | 0 | 0 |
1 | 0 | 1 | 0 |
1 | 1 | 0 | 0 |
1 | 1 | 1 | 1 |
As mentioned in the OR
function section, you can also consider 0
as a FALSE
value and 1
as a TRUE
value. As you can observe if any input has a FALSE
value then the output of the logical AND
function is FALSE
. However, if all the inputs are TRUE
then the output becomes TRUE
.
The syntax of the logical AND
function is given below:
1=AND(logical_value1, logical_value2, ... )
To learn how to implement the AND
function in Excel, consider the same student attendance scenario but this time assume that if a student is absent even a single day then he/she will be marked absent (FALSE
value).
This time, cell E1
will hold the following formula: =AND(B1, C1, D1)
. This will result in the following attendance sheet where only one student is marked present.
A | B | C | D | E | |
---|---|---|---|---|---|
Student list | Day 1 | Day 2 | Day 3 | Attendance | |
1 | Student 1 | TRUE | FALSE | TRUE | FALSE |
2 | Student 2 | TRUE | TRUE | TRUE | TRUE |
3 | Student 3 | TRUE | FALSE | TRUE | FALSE |
4 | Student 4 | TRUE | TRUE | FALSE | FALSE |
5 | Student 5 | FALSE | FALSE | FALSE | FALSE |
The logical NOT
function has the following truth table:
Input | Output |
---|---|
TRUE | FALSE |
FALSE | TRUE |
As you can observe from the table, the NOT
function inverts a given logical input.
Consider you have data of people's eating preferences which includes their Veg
and Non-Veg
type inputs, as shown in the table below. How can you select those people who prefer Non-Veg
?
A | B | |
---|---|---|
Food Preference | Result | |
1 | Veg | ? |
2 | Non-Veg | ? |
Use the following syntax to implement the NOT
function:
1=NOT(logical_condition)
So, you can write a condition to check if peoples’ food preference is Veg
and then later pass the result to the NOT
function. To achieve this, you can write =NOT(A1="Veg")
in the cell B1
which will give you the following result:
A | B | |
---|---|---|
Food Preference | Result | |
1 | Veg | FALSE |
2 | Non-Veg | TRUE |
A logical XOR
function follows the given truth table:
Input A | Input B | Input C | Output |
---|---|---|---|
0 | 0 | 0 | 0 |
0 | 0 | 1 | 1 |
0 | 1 | 0 | 1 |
0 | 1 | 1 | 0 |
1 | 0 | 0 | 1 |
1 | 0 | 1 | 0 |
1 | 1 | 0 | 0 |
1 | 1 | 1 | 1 |
To understand the above table, consider two inputs at a time. If there is the same value in both inputs then the result is FALSE
(or 0
). Next, use this result of the first two inputs, then take the third input and perform the same action. To illustrate this, take the last row which has 1
for all the inputs. Here, Input A and B has 1
and 1
respectively which gives 0
. Now, combine this 0
and Input C (1
). As you can observe, this time you have different values; hence, the output is 1
.
To illustrate this in Excel, consider an example where a child is presented with three different food items. At one time, she is given a choice between two food items and she can't choose both. Once the choice from the first two is made then the third item is presented for a final choice.
A | B | C | D | |
---|---|---|---|---|
Candy | Ice-cream | Chocolate | Result | |
1 | TRUE | FALSE | FALSE | ? |
2 | TRUE | FALSE | TRUE | ? |
3 | FALSE | FALSE | FALSE | ? |
4 | TRUE | TRUE | TRUE | ? |
The syntax for the logical XOR
in Excel is:
1=XOR(logical_value1, logical_value2, ... )
To solve the given scenario, we can implement the XOR
function starting with =XOR(A1, B1, C1)
formula in the cell D1
and stretching it to the subsequent rows. This results in the given table:
A | B | C | D | |
---|---|---|---|---|
Candy | Ice-cream | Chocolate | Result | |
1 | TRUE | FALSE | FALSE | TRUE |
2 | TRUE | FALSE | TRUE | FALSE |
3 | FALSE | FALSE | FALSE | FALSE |
4 | TRUE | TRUE | TRUE | TRUE |
The logical IF
function checks for a given condition. If the condition holds TRUE
, the first argument after the conditional argument is returned or else the second argument after the conditional argument is returned.
The syntax of the IF
function in Excel is given below:
1=IF(condition, true_value, false_value)
Consider a case where you are provided with the data of students’ marks. You need to mark a student as failed if their total marks are less than, or equal to, 150 out of 500.
A | B | |
---|---|---|
Total marks | Result | |
1 | 425 | ? |
2 | 125 | ? |
3 | 325 | ? |
4 | 441 | ? |
5 | 87 | ? |
6 | 222 | ? |
This can be achieved by using the logical IF
function. You can write the given formula =IF(A1 <= 150, "FAIL", "PASS")
in the cell B1
and apply the formula in the subsequent rows to achieve the following result:
A | B | |
---|---|---|
Total marks | Result | |
1 | 425 | PASS |
2 | 125 | FAIL |
3 | 325 | PASS |
4 | 441 | PASS |
5 | 87 | FAIL |
6 | 222 | PASS |
The logical IFEEROR
function is used to catch an error and handle it. If the expression doesn't result in the error then the expression is evaluated, or else the second argument is displayed.
The syntax of the IFERROR
function in Excel is given below:
1=IFERROR(expression, response_on_error)
Consider an example where you are given two inputs (both numeric) and you need to divide input A by input B. We will try to arrive at a Zero Division Error and, wherever it occurs, we replace the output with value 10
. Here's the data to start with:
A | B | C | |
---|---|---|---|
Input A | Input B | Output | |
1 | 54 | 23 | ? |
2 | 21 | 45 | ? |
3 | 789 | 2 | ? |
4 | 98 | 0 | ? |
5 | 451 | 584 | ? |
6 | 65 | 12 | ? |
To solve this problem, we can use the IFERROR
function by writing its formula in cell C1
as =IFERROR(A1/B1, 10)
which results in the following table:
A | B | C | |
---|---|---|---|
Input A | Input B | Output | |
1 | 54 | 23 | 2.347826087 |
2 | 21 | 45 | 0.466666667 |
3 | 789 | 2 | 394.5 |
4 | 98 | 0 | 10 |
5 | 451 | 584 | 0.772260274 |
6 | 65 | 12 | 5.416666667 |
The logical IFNA
function is mostly similar to the IFERROR
function except for the error it checks for a #N/A
value i.e., a missing value.
Note - A missing value is not similar to a blank value.
The syntax for the IFNA
function is given below:
1=IFNA(expression, value_if_NA)
Consider a scenario where you have a missing value as well as a blank value in your input data and then implement the IFNA
function. For a missing value, you should pass the output value as a NULL value
. Here's the data to experiment:
A | B | |
---|---|---|
Input | Output | |
1 | #N/A | ? |
2 | ? | |
3 | #N/A | ? |
4 | 0 | ? |
5 | 584 | ? |
6 | 12 | ? |
You can implement the formula =IFNA(A1, "NULL value")
in the cell B1
to get the following output. Now, observe the output for cell B2
.
A | B | |
---|---|---|
Input | Output | |
1 | #N/A | NULL value |
2 | 0 | |
3 | #N/A | NULL value |
4 | 0 | 0 |
5 | 584 | 584 |
6 | 12 | 12 |
The IFS
function checks for multiple expressions in one go, as compared to the simple IF
function. The syntax is given below:
1=IFS(expression1, value_if_expression1_is_true, expression2, value_if_expression2_is_true, ...)
The SWITCH
function "evaluates one value (called the expression) against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned," according to Excel's documentation. The syntax is given below:
1=SWITCH(condition, value1, response1, value2, response2, ..., default)
These two functions are left for you to tryout on your own!
In this guide, you have learned about the various logical functions available in MS Excel 2019 like TRUE
, IF
, OR
, NOT
, etc.