Introduction

5

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:

- TRUE
- FALSE
- OR
- AND
- NOT
- XOR
- IF
- IFERROR
- IFNA
- IFS
- SWITCH

In this section, you will learn about each of these logical functions through various scenarios.

We use the `TRUE`

and `FALSE`

functions when we want to showcase if a given condition is met or not. For instance, `5 < 3`

is a correct condition, hence the `TRUE`

function when used with the `IF`

function (or any other function) returns a `TRUE`

value. Had the condition been wrong, it would had 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`

.

Let us consider a scenario where you can learn how to implement the logical `OR`

function in Excel. We take five students attendance who have registered for a workshop. The final attendance is marked only when student is present 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, we provide the following logical `OR`

formula with syntax:

`1`

`=OR(logical1, logical2, ... )`

Therefore, let us 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(logical1, logical2, ... )`

To learn how to implement the `AND`

function in the Excel, let us take the same student attendance scenario but this time let us consider 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. Let us take a scenario to understand how to use it in Excel.

You have a data of people preferences which even includes their `Veg`

and `Non-Veg`

type inputs, as shown in the table below. How can you select people who are `Non-Veg`

?

A | B | |
---|---|---|

Food Preference | Result | |

1 | Veg | ? |

2 | Non-Veg | ? |

To implement the `NOT`

function, here's the syntax:

`1`

`=NOT(logical condition)`

So, we 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, let us 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, let us 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(logical1, logical2, ... )`

To solve the given scenario, we can implement `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 result: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 condition argument is returned or else the second argument after the condition 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 formula doesn't result in the error then the formula is evaluated, or else the second argument is displayed.

The syntax of the `IFERROR`

function in Excel is given below:

`1`

`=IFERROR(value, value_if_error)`

Let's 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)`

Let us take a scenario where we have a missing value as well as a blank value in our input data and then implement the `IFNA`

function. For the missing value, let us give 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. Let’s 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. The syntax is given below:

`1`

`=SWITCH(Value to switch, Value to match1...[2-126], Value to return if there's a match1...[2-126], Value to return if there's no match)`

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.

5