In this article we are going to learn logical function of Excel. Along with we will define and give examples of each logical function.
Microsoft Excel we know offer a lot of function, and one of these are logical functions which the purpose is to perform a comparison of data along with logical operations.
What are Logical Function in Excel?
Microsoft Excel has four logical functions which can work with logical values. These functions are utilized to handle more than one comparison of values, or formulas that require one or more conditions. As well as logical operators, Excel logical functions return either TRUE or FALSE when their arguments are evaluated.
These are AND, OR, XOR, and NOT. So there is the following summary of these logical functions to help you choose the right formula for a specific task.
| Function | Description | Formula Example | Formula Description |
| AND | This will returns TRUE if all of the arguments evaluate as TRUE. | =AND(F2<=75, B2<70) | The formula returns TRUE if a value in cell F2 is less than or equal to 75, and a value in B2 is less than 70, FALSE otherwise. |
| OR | If any argument is TRUE it will return TRUE. | =OR(F2>=75, C2<75) | The formula will return TRUE if A2 is greater than or equal to 75 or C2 is less than 75, or both conditions are met. If neither of the conditions is met, the formula returns FALSE. |
| XOR | If all arguments are exclusively logical OR. | =XOR(F2>=75, D2<75) | The formula returns TRUE if either F2 is greater than or equal to 75 or D2 is less than 75. If none of the conditions is met or both conditions are met, the formula returns FALSE. |
| NOT | This will return the reversed logical value of its argument. | =NOT(F2>=75) | The formula returns FALSE if a value in F2 is greater than or equal to 75; and vice versa. |
Excel AND Logical function
The AND function of Excel comes in very handy which makes it most popular among the logical functions family. It’s used to test and meets several conditions to ensure all of them are met.
So, the AND function is for testing the specified conditions and if the evaluated condition is all TRUE then it will return TRUE otherwise it will return FALSE.
The syntax of this function is shown below:
=AND(logical1, [logical2], …)
The first argument of this syntax is required while the succeeding logical conditions are optional. Technically the logical condition to test can be evaluated as either TRUE or FALSE.
And now, let’s examine the formula examples that indicate how to use the AND functions in Excel formulas.
| Formula | Description |
=AND(A2="Andrie", B2>D2) | Returns TRUE if A2 contains “Andrie” and B2 is greater than D2, otherwise FALSE. |
=AND(F2>80, F2<E2) | Returns TRUE if F2 is greater than 80 and F2 is less than E2, otherwise FALSE. |
=AND(A2="Andrew", F2<=75, C2>B2) | Returns TRUE if A2 contains “Andrew”, F2 is less than or equal to 75 and C2 is greater than B2, otherwise FALSE. |
And now we will use this formula and the following steps you can consider as follows.
Step 1: Prepare your numeric data in your worksheet which we will use AND function and see how it works.
Step 2: Enter this formula in a cell you select and with two conditions here.
=AND(B2>C2, A2="Andrie")
Step 3: Hit Enter to see what result we will get with the condition we input.
Hence, if it returns FALSE the first condition does not meet the first condition which B2 is greater than to C2 value. Where in we mention already AND function requires all conditions to be TRUE.
Step 4: And now, let us use again the AND function and apply it to another row of data. Below is the formula you will try with its single argument.
=AND(B3<=88, C3<=90)
Step 5: Hit the Enter key again and you will see the result, it returns TRUE as both conditions are evaluated as TRUE.
OR function in Excel
The Excel OR function is a basic logical function that is used to evaluate statements or values. The only difference of this to AND function is if at least one statement is True it will return TRUE otherwise it will return FALSE or if all the arguments are FALSE.
Additionally, this function is available in the 2016 – 200 Excel version.
The syntax of this function is as follows:
=OR(logical1, [logical2], …)
In the same manner, the logical arguments’ test could either be TRUE or FALSE. Wherein the first argument is required and the subsequent are optional, which can up to 255 conditions in modern excel versions.
And now, let’s write down a few formulas for you to get a feel for how the OR function in Excel works.
| Formula | Description |
=OR(A2="Jane", A2="Andrew") | Returns TRUE if A2 contains “Jane” or “Andrew”, otherwise returns FALSE. |
=OR(B2>=55, C2<=97) | Returns TRUE if B2 is greater than or equal to 55 or C2 is less than or equal to 97, otherwise return FALSE. |
=OR(F2=" ", A2="") | Returns TRUE if either F2 or A2 is empty or both, FALSE otherwise. |
Excel XOR function
Microsoft 2013 introduced this function XOR which is the exclusive OR function. So if you have any knowledge of programming languages you may encounter and familiar with this function. Meanwhile for those who are not and do not have any prior engagement with this function, the explanation below will give you an idea of how this function works.
The XOR syntax is given below:
=XOR(logical1, [logical2],…)
Where the first logical1 argument is required, the rest argument is optional. Luckily you can have up to 254 conditions you can test in just a single formula.
In simple understanding XOR function consists of two logical statements that return the following:
- FALSE if all arguments are TRUE or neither is TRUE.
- TRUE if at least one argument evaluates to TRUE.
This might be simple to understand from the formula examples:
| Formula | Result | Description |
=XOR(10<0, 5>2) | TRUE | Returns TRUE because the 1st argument is TRUE and the 2nd argument is FALSE. |
=XOR(1>0, 3<5) | FALSE | All the arguments are FALSE therefore it returns FALSE. |
=XOR(10>20, 20<1) | FALSE | Returns FALSE because both arguments are TRUE. |
Now we will try this formula in our worksheet to test the logical test.
See the formula in the image below:
Supposedly we will have the result of TRUE.
NOT function in Excel
This NOT function in Excel is one of the simplest function, as well as its syntax.
=NOT(logical)
The arguments of this function reverse the value. In simple words, if the logical evaluates to TRUE the Not function will reverse it and return to FALSE, and vice versa.
Below is the example of XOR formula which works as we want:
Here we do the nest in XOR function into the logical IF function:
Conclusion
In conclusion, logical functions in excel are helpful in worksheets especially when a formula required conditions that should be evaluated as TRUE or FALSE.
What do you think about this, feel free to leave your insights below, if you want to learn more about excel functions visit other tutorials like this.
Thank you for reading!