Logical functions in Excel are functions that test whether a specified condition is true or false. The result of the logical test will determine whether to evaluate or not to evaluate a function. For example, based on the result of the test, we can evaluate a function, display information, or display TRUE or FALSE.
There are several logical functions in excel, but the most used are the AND, OR, NOT, and XOR functions. In this tutorial, we are going to explore how to use the AND, OR, NOT, and XOR logical functions in Excel.
Logical Functions in Excel
What are logical functions in Excel?
When there is a need to make a decision before evaluating data in Excel, logical functions will be employed. Logical functions in Excel are decision-making tools used to analyze excel data. When it is employed, it tests a cell and returns a value based on the correct decision.
The AND, OR, NOT, and XOR logical functions return a TRUE or FALSE value depending on the decision evaluated. To evaluate a function and return a value, these functions are used alongside the IF function.
For example, the formula: =IF(AND(C3=”South”,D3=”Uche Golden”),”Correct”,”Wrong”) will return CORRECT or WRONG depending on the values of C3 and D3. In this tutorial, we shall discuss how to use the four logical functions displayed in the table below.
| Function | Detail | Example | Remark |
| AND | Evaluates to TRUE if all arguments are true | =AND(C3=”South”,D3=”Uche Golden”) | Returns TRUE only if C3 is South and D3 is Uche Golden |
| OR | Evaluates to TRUE if any of the arguments are true | =OR(C3=”South”,D3=”Uche Golden”) | Returns TRUE if C3 is South or D3 is Uche Golden or one of them is true or both of them are true. |
| NOT | Evaluates to TRUE if the argument is false, and vice versa | =NOT(C3=“South”) | Returns TRUE only if C3 is not South. I.e. if C3 is West, it returns TRUE. |
| XOR | Evaluates to TRUE only if the argument is an exclusive OR | =XOR(C3=”South”,D3=”Uche Golden”) | Returns TRUE if C3 is South and D3, not Uche Golden, or if C3 if not South and D3 are Uche Golden. |
Facts about logical functions in excel
When using the logical functions in Excel, you should attempt to observe the following important facts.
- Microsoft Excel 2003 version and lower accepts up to 30 arguments of a logical function. However, the formula length shall not exceed 1024 characters.
- Microsoft Excel 2007 and higher accepts up to 255 arguments of a logical function. But the formula length shall not exceed 8192 characters.
- Logical functions accept any of the following arguments: Mcrosoft Excel functions, numbers, texts, cell references, Booleans, and comparison operators.
- Logical functions do not evaluate a value but return a Boolean value of TRUE or FALSE.
Using the AND, OR, NOT, and XOR Functions in Excel
The AND, OR, NOT and XOR statements return a TRUE or FALSE value depending on the result of the logical test. They use the following arguments:
AND [=AND(logical1,logical2,logical3,…)] – A TRUE is returned if all the logical values are true else FALSE is returned.
OR [=OR(logical1,logical2,…)] – A TRUE is returned if one or both of the logical values are true else FALSE is returned
NOT [=NOT(Logical)] – A TRUE is returned if the logical value is true else FALSE is returned. A logical value is true when the value is not present in the referenced cell.
XOR [=XOR(logical1,logical2,…)] – A TRUE is returned if one of the logical values are true else FALSE is returned.
AND function in Excel
The AND function compares more than one condition in the function’s argument. It returns TRUE only if all conditions in the argument are met, and false, if otherwise. This means that when three conditions are specified, the AND returns TRUE if all three conditions are true. If one of the conditions is not true, the AND function returns FALSE.
Let us assume two conditions, A and B in an argument, the AND TRUE table is as follow: =AND(A2=“A”, B2=“B”)
| Condition A | Condition B | AND Value |
| A is true | B is true | TRUE |
| A is true | B is false | FALSE |
| A is false | B is true | FALSE |
| A is false | B is false | FALSE |
The syntax of the AND function is given as follow:
=AND(logical1, [logical2],…)
- Where AND is the Microsoft Excel function to be evaluated
- Logical1 is a required argument that must be specified. It is the condition that will be tested and it evaluates to either TRUE or FALSE.
- [logical2] is anoptional argument. When specified, it increases the chances of the argument evaluating to FALSE.
- The optional argument can be increased to up to 254 if the length of the formula is below 8192. Hence, you can have logical3, logical 4, … logicaln.
The AND function in Excel Example
- The table below displays the departments and salaries of different staff of a named organization. Using the AND function, identify the staff in the clerk department whose salary is below 10000.
To solve the above problem, do the following:
- Enter the required data in Excel as shown in the worksheet above.
- Create a column called “Clerks that earn 10000 and below”
- Enter the formula in cell D2, =AND(C2=”Clerk”,B2<=10000)
- Use the autofill handle to fill the formula to other cells below D2.
- The result of the function is displayed below
The function displays TRUE if both conditions, “Clerk” and <=10000 are true, and false if otherwise. You can use the AND function to identify ADMIN staff who earns above 10000.
OR function in Excel
The OR function also compares more than one condition in the function’s argument. It returns TRUE if any of the conditions in the argument are met. It, however, returns FALSE if none of the conditions are met.
This implies that when three conditions are specified, the OR function returns TRUE if any or all three conditions are true. If none of the conditions is true, the OR function returns FALSE.
Let us assume two conditions, A and B in an argument, the OR TRUE table is as follow: =OR(A2=“A”, B2=“B”)
| Condition A | Condition B | OR Value |
| A is true | B is true | TRUE |
| A is true | B is false | TRUE |
| A is false | B is true | TRUE |
| A is false | B is false | FALSE |
The OR logical function in Excel returns false only when none of the arguments is true.
The syntax of the OR function is given as follow:
=OR(logical1, [logical2],…)
- Where OR is the Excel function to be evaluated
- Logical1 is a required argument that must be specified. It is the condition that will be tested and it evaluates to either TRUE or FALSE.
- [logical2] is anoptional argument. When specified, it increases the chances of the argument evaluating to TRUE.
- The optional argument can be increased to up to 254 if the length of the formula is below 8192. Hence, you can have logical3, logical 4, … logicaln.
The OR function in Excel Example
- The table below displays the departments and salaries of different staff of a named organization. Using the OR function, identify the staff in the Admin department or those who earn above 10000.
To solve the above problem, do the following:
- Use the same data you used in the AND function example
- Create a new column called “Admin Staff and those who earn above 10000”
- Enter the formula in cell E2, =OR(B2>10000,C2=”Admin”)
- Use the autofill handle to fill the formula to other cells below E2.
The result of the function displays TRUE if any of the conditions are true. Here, if staff is in ADMIN, OR evaluates as TRUE, if the salary is >10000, OR evaluates as TRUE. Also, if staff is admin and salary above 10000, OR evaluates as TRUE. But if staff is not ADMIN, and salary is not above 10000, OR evaluates to FALSE.
NOT function in Excel
The NOT function is used to exclude the values you do not want in a function. It returns the opposite of the logical condition in the function’s argument. For example, if the logical test is TRUE, the NOT function returns a FALSE value and vice versa.
The NOT function has only one argument which can be a text, cell reference, etc. Any value in the argument is excluded when the function is evaluated. For example, =NOT(A2=“Blue”) means every other color except blue.
The NOT TRUE table is as follow: =NOT(A2=“A”)
| Condition A | OR Value |
| A is true | FALSE |
| A is false | TRUE |
The NOT logical function in Excel returns false only when the argument in the function is true.
The syntax of the NOT function is given as follow:
=NOT(logical)
- Where NOT is the Excel function to be evaluated
- logical is a required argument that must be specified. It is the condition that will be tested and it evaluates to either TRUE or FALSE.
The NOT function in Excel Example
- The table below displays the departments and salaries of different staff of a named organization. Using the NOT function, identify the staff that is not in the Accounts department.
To solve the above problem, do the following:
- Use the same data you used in the AND function example
- Create a new column called “Staff not in ACT Dept.”
- Enter the formula in cell F2, =NOT(C2=”Act”)
- Use the autofill handle to fill the formula to other cells below F2.
The result of the function displays TRUE if the condition is false, and FALSE if the condition is true. Here, if staff is in Admin, Clerk, or Messenger, NOT evaluates as TRUE, if staff is in ACT, NOT evaluates to FALSE.
XOR function in Excel
This is called the exclusive OR function. The XOR function is used to compare more than one condition in the function’s argument. It returns TRUE if any of the conditions in the argument is met. It, however, returns FALSE if both or none of the conditions are met.
This implies that when three conditions are specified, the XOR function returns TRUE if any of all three conditions are true. If none or all of the conditions are true, the XOR function returns FALSE.
Let us assume two conditions, A and B in an argument, the XOR TRUE table is as follow: =XOR(A2=“A”, B2=“B”)
| Condition A | Condition B | OR Value |
| A is true | B is true | FALSE |
| A is true | B is false | TRUE |
| A is false | B is true | TRUE |
| A is false | B is false | FALSE |
The XOR logical function in Excel returns false only when all or none of the arguments is true.
The syntax of the XOR function is given as follow:
=XOR(logical1, [logical2],…)
- Where XOR is the Excel function to be evaluated
- Logical1 is a required argument that must be specified. It is the condition that will be tested and it evaluates to either TRUE or FALSE.
- [logical2] is anoptional argument. The optional argument can be increased to up to 254 if the length of the formula is below 8192. Hence, you can have logical3, logical 4, … logicaln.
The XOR function in Excel Example
- The table below displays the departments and salaries of different staff of a named organization. Identify those who are exclusively in the Mess department or earns less than 10000.
Let’s use the XOR function to identify those in the Mess dept. or those who earn below 10000.
- Use the same data you used in the AND function example
- Create a new column called “Mess Staff or those who earn below 10000”
- Enter this formula in cell G2, =XOR(B2<10000,C2=”Mess”)
- Use the autofill handle to fill the formula to other cells below G2.
The result of the function displays TRUE if any of the conditions are true. Here, if staff is in Mess and salary 10000 and above, XOR evaluates as TRUE. If salary is <10000 and staff not in Mess dept., XOR evaluates as TRUE. But, if staff is in Mess and salary below 10000, XOR evaluates as FALSE. Also, if staff is not in Mess and salary above 10000, XOR evaluates to FALSE.
Conclusion
Logical functions in Excel are used to test the conditions of an argument whether it is TRUE or FALSE. There are different built-in logical functions in Excel, but the most used ones are AND, OR, NOT, and XOR.
Each of the logical functions evaluates to TRUE or FALSE. To return the actual value, the IF function will be employed.
The AND, OR, and XOR functions compare more than one condition while the NOT function excludes specified conditions.
It is important to master these functions because we shall use them with the IF Statement to return values. Before we continue with the IF Statement in Excel next week, remember to visit our previous tutorials. Don’t forget to share this tutorial with friends.
