Logical Functions in Excel – AND, OR, XOR, NOT

Logical functions and formulas help the user to make decisions on the dataset. The function helps in combining multiple conditions and it also helps to evaluate whether a condition is true or false. 

A condition is an expression that evaluates whether it is true or false. The expression also helps in determining the data type of the cell. It also helps in checking if the value in the cell is greater than, lesser than or equal to a specified value.

Logical Functions in Excel

Logical functions are being used widely in MS Excel worksheet to evaluate and analyze the data present in the sheet.

Let’s look at some important functions in MS-Excel. These functions and formulas in excel help to analyze and evaluate the data in an organized manner. 

Logical functions and its description

Let’s get into the IF formula and its parameters.

IF Function in Excel

The IF Function is one of the logical functions and the user can use IF statements on the worksheet to evaluate the values to true or false. If it meets the user condition then it shows the output as true or else it returns false. The IF function can also combine with the functions such as AND, OR. 

Note: 

• IF function returns 0 or 1 if no value is provided in the place of value_if_true, value_if_false. 
• Add argument of value_if_true, value_if_false to see if the right value returns.
• If #NAME error occurs, it means the formula is misspelled. 
• The user can nest upto 64 IF functions together. 
• Every element of the array is evaluated when IF condition is carried out in an array.

Excel Formula

=IF(logical_test, [value_if_true], [value_if_false])

In the if function, we have three parameters to fill. The three parameters are:

a. logical_test – A logical expression that evaluates as true or false. This is a required parameter.

b. value_if_true – This is one of the optional parameters. Whatever the value we provide here is returned as true if logical_test satisfies.

c. value_if_false – This is also one of the optional parameters. Whatever the value we provide here is returned as false if logical_test does not satisfy.

Let’s look at a sample without providing the optional parameters:

In the above sample, we have not provided any value in the value_if_true and value_if_false criteria. Since we haven’t provided anything, the excel sheet itself considers it as 1 and 0. If it meets the condition, then 1 is shown else it shows as 0. 

Let’s look at a sample of IF function:

In this sample, we are going to see whether the EB Cost of each house is more than 1000 rupees. If it’s more than 1000 then it prints yes or else it prints no in the specific cell.

Observe the formula of the D3 cell in the formula bar. Since, the EB cost is 1250, it prints yes as provided in the formula parameters.

Note: The value that we enter after the logical_test parameter will be the value which will show up, when the criteria satisfies.

If with calculation in Excel

If the function can also be used for the purpose of calculation. Let’s see a sample to understand it more briefly

In this sample, we are adding 100 if the consumption is less than 100 units or else we are subtracting 100 from it.

If with AND in Excel

Excel syntax: IF(AND(condition 1, condition 2), “value_if_true”, “value_if_false”)

Excel Formula =IF(AND(B3>100,C3>1000),”YES”,”NO”)

• If both condition 1 and condition 2 evaluates to TRUE, then the value that function returns is “YES”.
• If any of the conditions or every condition evaluates to FALSE, then the value that function returns is “NO”.

Now, let’s look at how to apply and criteria with an if function. Look at the below example to understand it.

In the above sample, we are trying to see in which all houses there were more than 100 units of consumption and the EB Cost is above 1000. To apply this condition, we are using AND criteria in the if function. 

If with OR in Excel

Now, let’s try to implement OR criteria with the if function. 

Look at the sample below:

In this sample, we are checking if the units are more than 50 or 150. In the D3 cell, the result has come “No” because the units utilized in that house is only 25. It does not satisfy both conditions. Hence, the value is no.

Nested IF in Excel

Nested IF is a function that contains many IF statements inside an IF function. We can also say it as the function starts with If function and other IF functions are included inside the original IF function. 

Nested IF formula: =IF(logical_test,[value_if_true],[value_if_false],IF(logical_test,[value_if_true],[value_if_false], …))

Let’s look at the concept of using an if function inside an if function itself. Have a look at the sample to understand it briefly:

In this sample, we are trying to implement multiple if conditions in one cell. This helps in evaluating the cells. Here, we are checking whether the cost is low, moderate or high using nested if.

If the value is below 500, the cost type is low, if the value is below 1000, the cost type is moderate and if the value is  above 1000, then its cost type is high.

IFS Function in Excel

IFS is a function which contains one or more arguments inside a function. The  user uses IFS statements to evaluate multiple conditions without using many if statements. It is much simpler to read and it results in the value corresponding to the first true statement. 

Syntax

IFS(Logical_Test1, value_if_true1, [Logical_Test2, value_if_true2], [Logical_Test3, value_if_true3],…)

Arguments:

Logical_Test1: It is one of the required parameters and it is the condition that evaluates to true or false. 

Value_if_true1: It is also one of the required parameters in the function and it can be empty also. This is the result that will be returned if the logical_test1 evaluates to true.

The other arguments are optional, the logical_test arguments are the conditions that evaluate to true or false and the value_if_true arguments are the value to be returned if the logical_test conditions are true.  

Example:

In this sample, we are trying to implement multiple if conditions in one cell. This helps in evaluating the cells. Here, we are checking whether the units consumed are low, moderate or high using an IFS statement.

If the value is below 20, the units consumed is low, if the value is below 300, the units consumed is moderate and if the value is below 1000, then its units consumed is high.

Notes

• The functions are limited to 254 parameters and so the user can have up to 127 pairs of arguments.
• If none of the conditions are met and to overcome the error, enter a condition that will always be true at your final logical_test argument. 
• If no TRUE conditions are found, IFS returns #N/A! Error.
• The #N/A! Error occurs if no condition evaluates to true. 
• The #VALUE! Error occurs when the logical_test argument resolves to a value other than true or false. 

AND Function in Excel

The AND Function is one of the logical functions and the user uses the AND statements on the worksheet to evaluate the values to true or false. If the user enters more than one argument to evaluate and only if all the condition satisfies, it results true or else it returns false.

The AND function returns true only if all of the arguments satisfies.  

Excel Formula: AND(Logical Argument1, Logical Argument2, Logical Argument3,..)

Argument:

• Logical Argument 1: It is the first condition to evaluate.
• Logical Argument 2: It is the second condition to evaluate.

NOTE:

1. Values are ignored if the array contains text or empty cells.

2. The function returns the #VALUE! Error when the specified range has no logical values.

How to Open AND Function in Excel?

To open AND function in Excel, follow the steps:

1: Click on the Formulas tab and choose the logical icon from the ribbon.

Note: The list of functions appears.

2: Choose AND function from the list of functions available.

Let’s look at a sample:

In this sample, we are trying to check whether the cell satisfies both conditions. If it does, then the result appears as true or else false.

In the D3 cell, the value is false because the B2 cell value is less than 200. Hence, the value appeared false as the condition didn’t satisfy.

OR function in Excel

The OR function is one of the logical functions and the user uses the OR statements on the worksheet to evaluate the values to true or false. If the user enters more than one argument to evaluate and if any of the conditions satisfy, it results true, else it returns false.

The OR function returns true even if one of the arguments satisfies.  

Excel Formula: OR(Logical Argument1, Logical Argument2, Logical Argument3,..)

• Logical Argument 1 – the condition which we want to evaluate and returns either true or false.
• Logical Argument 2 – This condition is the optional, second condition which we want to evaluate for TRUE or FALSE.

NOTE:

1. Values are ignored if the array contains text or empty cells.

2. The function returns the #VALUE! Error when the specified arguments are text values. It also returns errors when there are no logical values. 

3. To evaluate an array with OR formula, press Ctrl+Shift+Enter.

When to use OR Function?

The user can use OR function when they want to check multiple conditions in a defined cell.

How to Open OR Function in Excel?

To open OR function in Excel, follow the steps:

1: Click on the Formulas tab and choose the logical icon from the ribbon.

Note: The list of functions appears.

2: Choose OR function from the list of functions available.

How to use OR Function in Excel?

The OR Function is very easy and much simpler to use. The user can manually enter the formula by using the dialogue box and enter the required logical conditions according to the requirement in the spreadsheet.

Now, let’s try implementing the OR function in the spreadsheet. Remember, this function returns the true value even if one condition meets the criteria.

Let’s try it out with a sample.

In this sample, we are checking whether the units are above 200 or below 700. Since, every B column cell is above 200. It returns the result as true for every corresponding cell because it has met its condition.

NOTE: In And criteria, both the conditions should meet its criteria whereas in the OR condition, meeting anyone criteria is enough to give the output as true.

IFERROR in Excel

If the formula throws an error then the user can specify a value to display instead of showing an error in the cell. This function could be widely helpful when the user runs into errors rarely and prefers to have a clean spreadsheet.

Excel formula: =IFERROR(value,value_if_error).

This function is used to bring out a meaningful value if the cell contains an error.

In this sample, we are printing meaningful information corresponding to the error cell using the iferror formula.

True & False:

These logical values return true if the condition is met or else it returns false.

Syntax

• TRUE()
• FALSE()

Arguments

There are no arguments for true & false functions.

Notes

The true & false function is provided in excel for compatibility purposes. 

Here, if the c column cells contain the value low, then it returns true or else it returns false. 

AND OR function in Excel

Let’s look at the sample where we use and function, or function together in a sample. 

Look at the sample below:

In this sample, we are trying to check whether any of the two conditions are met. If one criteria in both the or conditions is met, then the value returned will be true. Or else it returns false.

NOT Function in Excel

The not function simply returns the opposite of the logic we have provided. In the case of true, it comes out with false and vice versa.

Excel Formula =NOT (Logical Argument) 

• Logical Argument – It can be evaluated to TRUE or FALSE

The logical argument can be entered in the formula itself or it can be entered as a reference to a cell and the cell contains a logical value. This function always returns the boolean value i.e., either TRUE or FALSE. 

Let’s look at a sample to understand:

In this sample, we are checking whether the units are greater than 200. In the B3 cell, the unit is only 25 but still the output that we have got is true. And this is because it’s a NOT function, it simply reverses the answer. Since, the condition is false and not in the before, the value appears to be true. 

Example: #VALUE! Error

The error appears when the argument given is not a logical or numerical value.

Suppose, if the user gives a logical argument as the range. Then the function returns a #VALUE error because the NOT function does not allow any range and it can take only a single logical argument at a time.

Example – NOT Function for an empty cell or “0”

The “NOT” function returns TRUE if the cell is empty or it contains 0.

Cell B2 contains the value 0.  The user applies the NOT function referring to the B2 cell as a logical argument, the output returned is TRUE. 

Example – NOT Function for Decimals

The “NOT” function returns FALSE if the cell contains the decimal value.

The cell B2 contains the value 22.5 in the cell.  The user applies the NOT function referring to the B2 cell as a logical argument, the output returned is FALSE.

Example – NOT Function for Negative Number

The “NOT” function returns FALSE if the cell contains the negative number value.

Cell B2 contains the value -100.  The user applies the NOT function referring to the B2 cell as a logical argument, the output returned is FALSE. 

Example –  When the reference is Boolean Input

The “NOT” function returns FALSE if the cell contains the true value and if the cell contains false value then the ‘NOT’ function returns TRUE.

Cell A2 contains the value True.  The user applies the NOT function referring to the A2 cell as a logical argument, the output returned is FALSE. Cell A3 contains the value False.  The user applies the NOT function referring to the A3 cell as a logical argument, the output returned is FALSE.

Not function with Isblank

This function checks whether the cells are blank or not in the spreadsheet. If the cells are blank, then it returns false as we are using it along with a not function or else it returns true.

Not Function along with OR and IF function

We are going to implement the Not logical function with the other two logical functions. 

In this sample, the formula helps you to tell about the amount the house is going to pay, it’s either a less pay or a high pay. If the condition is true, then it returns less pay or else it returns as high pay as per the formula.

XOR function in Excel

In this xor function, the value will return true when the first criteria is alone true and even though the second argument is false,it returns true. The value returns false if both the criteria satisfies or if both the criteria are unsatisfied.

Syntax

xor(Logical1,[Logical2],..)

Arguments

• Logical1 – It is a required argument and it can be of logical value, arrays or references.
• Logical2.. – It is an optional parameter and the xor function can check upto 254 conditions.

NOTE:

1. Values are ignored if the array contains text or empty cells.

2. The function returns the #VALUE! Error when the specified arguments are text values. It also returns errors when there are no logical values. 

3. The function returns the #NAME? Error when the excel does not recognize the function name.

4. To evaluate an array with XOR formula, press Ctrl+Shift+Enter.

5. The XOR functions output is true if the number of true inputs is odd or if true inputs are even, then the output is false. 

From the above sample, we can observe the formula in the formula bar. In the D3 cell, the result is false because both the conditions are satisfied.

Summary

1. Utilization of logical functions in a spreadsheet will help you understand and analyze the data of your spreadsheet.

2. It’s widely used for decision-making purposes.

3. It results using default values such as true or false.

4. It assists in evaluating the data using formulas and functions.