Microsoft Excel offers powerful logical functions which are very useful in data interpretation, especially when it comes to large data sets. Few of the most commonly used logical functions are IF, AND, OR and NOT. Let’s learn the use of these logical operators in Excel with the help of an example.
Above is a list of students and their scores in math and reading. Based on this data below, we will understand the use and syntax of various Excel logical operators.
IF Function
The syntax for the IF function is IF(condition=TRUE, value_1,value_2)
This implies that if the condition defined in the IF function is true, the result will be value_1, else value_2.
Now, let’s see the example below. Let’s assume the passing score in Math is 60. So, any student who scored 60 or above will pass. The formula shown below in the formula bar will determine this value for cell D2. When dragged across column D, this formula will help determine the result for cells D2 through D7.
AND Function
The syntax for Excel AND operator is AND(condition_1,condition_2). If both conditions are true, the result will be TRUE, else FALSE.
Now, let’s assume that the overall result will be PASS only if the student received a passing score in both math and reading. The formula in cell F2 below uses the AND function to determine the result when both the defined conditions are true.
This is an example of a nested AND within an IF function.
AND could also have been used without the IF function here. However, since this is a logical operator, it would then yield the value as TRUE or FALSE. See formula below.
OR Function
The syntax for Excel logical OR operator is OR(condition_1, condition_2). As the name implies, if either of the conditions is true, the result will be TRUE, else FALSE.
Considering an alternate condition to the above, let’s now assume that a student will PASS if either the math or reading results is PASS.
Using OR function with IF, the formula in cell F2 as shown below determines whether the overall result is PASS or FAIL.
Alternatively, using the OR function alone will yield a boolean result as a TRUE or FALSE. See formula below.
NOT Function
Unlike the above operators in Excel, NOT is an Excel Boolean operator that takes a single condition to determine the Boolean output of TRUE or FALSE. It follows the syntax NOT(condition). This Excel function reverses the input TRUE to FALSE and vice versa.
The condition in OR above can also be presented using a NOT function to determine whether or not a student passed. See formula below.
When working with large data sets, these Excel logical operators can be really useful in analyzing data from different angles by adding simple conditions. Moreover, this analysis can also be used in projecting variable outcomes when certain conditions are altered.
Working on large data sets requires great accuracy and efficiency. Check out this interesting read on lesser-known yet smart Excel features here.
