Logical functions in Excel
Microsoft Excel offers inbuilt logical functions to perform logical operations on Excel data. Logical functions are basically used to perform comparisons on data. Logical functions offered by MS Excel are – AND, OR, XOR, NOT, TRUE, FALSE, IFNA, etc. Using these logical functions, you can compare data in different ways.
All the logical functions return a Boolean result depending on the data and function you use. It can be either TRUE or FALSE. In this chapter, we will help you to discuss and use each logical function in brief. Each of these functions works to be used in different situations.
Logical functions offered by MS Excel
MS Excel provides found logical functions (AND, OR, NOT, XOR) to work with logical values. These functions help when the user wants to test or compare more than one condition in the formula. As these are logical functions, they return a Boolean value (TRUE or FALSE) after evaluation.
Following is a list of all logical functions that you will see inside the Logical Functions dropdown list inside the Formula tab in the Microsoft Excel –
- AND
- FALSE
- IF
- IFERROR
- IFNA
- NOT
- OR
- TRUE
- XOR
These nine prebuilt functions are presented by the Excel application. We will brief all these functions, their uses and how to use them. You will see how these functions will help you out to compare data and get the needed result.
Before this, we have this brief summary about each logical function –
| Function | Description | Formula | Formula Description |
|---|---|---|---|
| AND | Return TRUE if all the conditions are found true. | =AND(A3>20, B3<=10) | After comparing the data, if both conditions are found true, it will return TRUE. |
| OR | Return TRUE if one of the conditions are found true. | =OR(A3>20, B3<=10) | After comparing the data, if one of the conditions is found true, it will return TRUE. It will return false if all conditions are not satisfied. |
| NOTReturn reverse of the evaluated condition. | E.g., if the condition is true, it will return FALSE. If the condition is false, it will return TRUE. | =NOT(A3<=15) | It will return TRUE if the value in A3 cell is not less than or equal to 15. If it is greater than 15 or equal to 15, it will return FALSE. |
| XOR | It refers to exclusive OR, which means it returns the reverse result to the OR function. | =XOR(A3>20, B3<=10) | This formula will return TRUE if either A3 is greater than 20 or B3 is less than or equal to 10. It will return FALSE when both of the conditions are met or both of the conditions are not met. |
You have seen the brief description for each logical function with a formula example. Now, we will implement them in an Excel sheet data so that you can learn by the actual implementation. This will better help to understand to use them and analyze their results.
Besides these four logical functions, we have also told you about a few more functions named IF, ISERROR, IFNA. These functions come under the conditional functions category.
Facts of Logical functions
Some facts and figures are described for the logical functions that will help you to understand how different values are compared using these functions and how their results are evaluated.
- In these logical functions, you can provide cell reference, number, or string as an argument. These functions return a Boolean result after evaluating the expression inside the logical function.
- If the argument in formula expression contains empty cells, such values are ignored by the logical functions. Suppose that – all cell references are empty inside the logical function, it returns #VALUE! Error.
- If you are using a logical function inside an older version of Excel that does not support it, Excel returns #NAME? Error. For example, XOR function is available in Excel 2013 and above versions.
- It also returns #NAME? Error when you misspelt the function name.
AND Logical function
The AND function is the most common and most used logical function in Excel. This is used commonly to check the several conditions to true. It ensures the users that all conditions are met by returning TRUE and when one of the conditions does not match, it returns FALSE.
Technically, it evaluates more than one logical expression by separating them by a comma inside the AND logical function. The syntax for the AND logical function will be something like –
The result return by this function is based on the expression you provided. As we already told you that – it will be either TRUE or FALSE.
You can use this formula either from the formula tab inside the logical function. Or if you are familiar with the formula, expressions, and its argument, you can directly type the formula in cell and get the result. We will show you both ways.
Example 1
Step 1: Following is the text and numeric data in the sheet on which we will apply the AND function to show how it works with different types of arguments.
Step 2: Write the following formula in which we are checking two conditions.
=AND(B2>C2, A2=”Speaker”)
Step 3: Let us see what is the result we will get for the above condition check by hitting the Enter key.
It returned FALSE as the first condition did not satisfy where 30 is not greater than to 35.
AND() logical function requires both the conditions to be true to get the resultant value as TRUE.
Step 4: Now, use the AND() function one more time and check another condition for row3 data. Apply the formula like below with only one argument this time:
=AND(B3>15, C3<=15)
Step 5: Hit the Enter key one more time and see the result that it returned TRUE as both conditions are stated inside this function are satisfied.
Apply AND function from formula bar
If you do not find it easy to write the formula, you can click and select UI to implement the same functionality. In the logical function list, all the logical functions reside where from you can use these formulas and apply them to your data.
We will continue with the previous data.
Step 6: Select a blank cell to store the result and go to the Formula tab in the Excel ribbon. Then click the Logical dropdown list.
Step 7: Select the AND function from the list of functions. A dialogue box to specify the condition for this function will open.
Step 8: Write one more condition inside the Logical1, Logical2, … fields and click OK. For example, we have written down the following conditions.
You can also notice the result for each condition at just right to their respective field.
Step 9: As both conditions are satisfied and their results are TRUE. So, it will now return TRUE after closing that window.
So, the conclusion is that even without writing the formula and knowing formula syntax, you can perform logical operations on Excel data by using inbuilt feature of Excel. Sometimes, it is time taking but easy for non-tech users.
OR Logical function
Just like the AND function, OR function is also a commonly used logical function in Excel. It can be used to check several conditions at once by providing them as an expression in arguments. It returns TRUE if it finds one of the conditions is satisfying inside this function. On the other hand, it returns FALSE if none of the expressions is matched with the condition.
Technically, it evaluates more than one logical expression by separating them by a comma inside the OR logical function. The syntax for the OR logical function will be something like –
The result return by this function is based on the expression you provided. As we already told you that – it would be either TRUE or FALSE.
Example
Step 1: Following is the text and numeric data in the sheet on which we will apply the OR function to show how it works with different types of arguments.
Step 2: The same conditions we will check for the OR function to get and see the difference between AND() and OR() function resultant data. So, write the formula in D2 cell as –
=OR(B2>C2, A2=”Speaker”)
Step 3: Hit the Enter key to get the result and see what it returned. It returned TRUE as one of the given conditions is satisfied.
The OR() function requires one of the expressions to be satisfied inside this function to get the result as TRUE.
Step 4: Now, we will provide both wrong conditions inside the OR function to check what should it return.
=OR(B3<20, C3=10)
Step 5: Hit the Enter key one more time and see that it returned FALSE as both conditions are stated inside this function are not satisfied.
When one or more conditions are true inside the OR logical function, it always returns TRUE. Just like the AND function, you can apply the OR function from the Formula tab.
The AND function can be used inside the OR function in a single formula and vice versa.
NOT Logical function
After the successful implementation of AND and OR functions, you will now see the implementation for the NOT and XOR functions. NOT function returns the reverse of the evaluated condition inside this function.
Basically, the NOT function is the simplest logical function of Excel and is easy to understand.
Syntax for NOT function
The syntax for this function is:
This condition argument can be an expression, Boolean value, or any condition you want to check.
Suppose that you provide TRUE inside the NOT function, it will return FALSE as a result. In a similar way, if you provide FALSE inside the NOT function, it will return TRUE. It will be the best way to understand the functionality of NOT logical function.
Example
Step 1: Following is the data in a sheet on which we will apply the NOT function to show how it works with different types of arguments.
Step 2: Write the following NOT logical function with a condition inside it and see what it will return after evaluation.
=NOT(B2>=60)
Step 3: You see that the B2 cell has a price 80 that is greater than 60. So, the function should return true, but as it is a reverse function, it returned FALSE.
“NOT function returns reverse of the evaluated condition.“
Step 4: Now, we will check for the other data. Write the following formula in D3 cell with the wrong condition and see what it will return.
=NOT(A3=”Apple”)
Step 5: The NOT function returned TRUE this time after evaluating the condition inside the function that is satisfying.
Step 6: Let us directly provide the TRUE inside the NOT function. It will return the reverse of the given expression.
Step 7: Hit the Enter key and get FALSE Boolean value as a result (reverse of the provided Boolean data).
XOR Logical function
XOR is another and last logical function of Excel that can be used to check the condition based on a certain scenario. It provides the reverse result of the OR function. This function was not available in an earlier version of Excel. It was added in Excel 2013 and is now available in all the above versions.
It is an exclusive OR logical function. You can implement it using UI that is available inside the Formula tab in the Logical function list.
Syntax for XOR function
The syntax for this function is:
This condition argument can be an expression, Boolean value, or any condition you want to check. After evaluating the logical condition inside the function, it returns the following result.
- The exclusive OR returns TRUE on finding either of the condition to be true.
- FALSE is returned when all the conditions evaluated are either true or false.
Example
Step 1: We have taken the following dataset on which we will implement the XOR function and learn the working of it.
Step 2: Write the XOR formula in D2 cell to check what it will return.
=XOR(B2>C2, A2=”Speaker”)
Step 3: Hit the Enter key to get the result and see what it returned. It returned TRUE as one of the given conditions is satisfied.
Step 4: Now, we will provide both wrong conditions inside the OR function to check what should it return.
=XOR(B3=20, C3<15)
Step 5: Hit the Enter key one more time and see the result that it returned FALSE as both conditions are stated inside this function are satisfied.
If both conditions find not to be satisfied, it even returns FALSE. This is how the XOR function works. These functions are used in different situations accordingly they need. Choose the function wisely, whichever you want to use.
IF function
IF function is placed inside the Logical function category in Excel. This is also used for condition checks. In all the above functions we have discussed above, we get the only Boolean result (TRUE or FALSE). IF function enables the users to check the condition as well as customize the result.
Regardless of the Boolean result, you can get customized results according to you. You can use the IF function with other functions. Following is the syntax for the IF function –
Syntax
Here, condition is mandatory to provide to be checked inside the IF function. If you want to display the customize message instead of TRUE or FALSE, use the remaining parameter.
Example
We will demonstrate the uses and working of the IF function. We have taken the such data for this example.
Step 1: Write the IF formula in a cell with customized resultant arguments inside it.
=IF(B2>33, “Pass”, “Fail”)
Step 2: When you now press the Enter key, you will get the result accordingly. If the mark in B2 cell is higher than 33, Pass string will be returned. Otherwise, fail will return.
Step 3: We will now check for another condition. This time we will check the condition that is not true.
=IF(B3>33, “Pass”, “Fail”)
Step 4: See that result for the above formula. We have received the String Fail as a resultant value. (Here, Fail means FALSE that returns when the condition does not satisfy)
Besides this, you can use the IF function with another logical function and make the condition check.
