Lots of people would have already used IF function in Excel and some would have used the Logical functions (AND, OR and NOT) of Excel. Few more would have known these functions, but, not used much. Especially the Logical functions as they rarely have a direct use case. However, when we combine these functions, they become very powerful.
Now, let’s have a look at each of the functions mentioned below individually and then we can understand how they can be combined:
- IF
- AND
- OR
IF Function
IF function is one of the most used Excel functions. It provides an output, based on a certain logical condition check. The Syntax is as follows:
=IF (logical_test, [value_if_true], [value_if_false])
logical_test – A logical expression that can be evaluated as TRUE or FALSE.
value_if_true – [optional] The value to return when logical_test evaluates to TRUE.
value_if_false – [optional] The value to return when logical_test evaluates to FALSE.
Logical conditions can be anything like a comparison between numbers or strings. Based on the result of the condition check either one of the two values will be returned as the output.
AND Function
AND function is a logical function which takes one or more logical values as the input and results in either TRUE or FALSE. The Syntax is as follows:
=AND(logical1,[logical2]….)
Logical1, logical2 and so on will be different logical expressions which evaluate to either TRUE or FALSE
The result of the formula will be TRUE only if all the inputs are TRUE. If any of the Inputs (Logicals) is FALSE will the result is FALSE.
OR Function
OR function is a logical function which takes one or more logical values as the input and results in either TRUE or FALSE. The Syntax is as follows:
=OR(logical1,[logical2]….)
Logical1, logical2 and so on will be different logical expressions which evaluate to either TRUE or FALSE
The result of the formula will be TRUE if any one of the inputs is TRUE. Only if all the inputs (Logicals) are FALSE will the result will be FALSE.
IF & AND Function
Given that we have individually looked into the IF, AND and OR functions. Let’s see how we can use them in combinations and unlock a more powerful use-case of the formulas. We’ll start with the combination of IF & AND logical function. In such an example you can use the AND function in the logical_test section of the if statement. So, the result of the AND acts as the logical value based on which the IF formula chooses one of the other two input parameters of the IF as the output.
Let’s take an example to understand this better. For simplicity, we’ll take an example related to Pass or Fail scenario of two courses. Let’s say the passing condition for Course A is a score above 70 while the passing condition for Course B is a score greater than 50. Use the following instructions to understand what can be done in such situation:
Note: You can download the Practice file here: Download Excel file
- Create the data as shown below
- Now we’ll write the formula mentioned below in D2 using an AND function in IF to determine the result based on the criteria discussed earlier
=IF(AND(B2=”Course A”,C2>70),”Pass”,IF(AND(B2=”Course B”,C2>50),”Pass”,”Fail”))
- Note that we have used a nested IF function along with the usage of AND condition
- In the first IF function, we use an AND condition to check if the course is Course A and if the score is greater than 70. In such a case we show the result as pass. If this condition is not met, we proceed to the second IF function
- In the second IF function, we use an AND condition to check if the course is Course B and if the score is greater than 50. In such a case we show the result as pass. If this condition is not met, we show the result as fail
- Drag the formula down till D11. Cross check your results with the screenshot below:
This is how you can use the logical function AND can be used along with the IF function to produce great results.
IF & OR Function
Now, we’ll look at how we can use the IF function with the OR logical function. For this, consider an example where the student has Passed if he/she scored 50 or more in either the project or the lab. In an OR function there can be multiple logical condition checks, even if one of the conditions is met then the output will be TRUE.
Use the following instructions to understand how the situation mentioned above can be handled:
Note: You can download the Practice file here: Download Excel file
- Create the data as shown below
- Now we’ll write the formula mentioned below in D2 using an OR function nested inside IF to determine the result based on the criteria discussed earlier
=IF(OR(B2>50,C2>50),”Pass”,”Fail”)
- We have used an OR condition in the logical test part of the IF statement
- The OR condition returns TRUE either if the Lab Score is greater than 50 or if the Project Score is greater than 50
- When the result of the OR is TRUE, the result of the IF statement will be a “Pass”. Otherwise, it results in “Fail”
- Drag the formula down till D11. Cross check your results with the screenshot below:
This is how you can use the IF and the OR functions together fur better results. You can download and refer to the solution file from the link below:
Please share
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
- Learn all about the new XLOOKUP Function in Excel! - September 27, 2019
- FOR EACH Loop in Excel VBA - December 15, 2018
In the example for the If & And Function the criteria is “the passing condition for Course A is a score above 50” and “the passing condition for Course B is a score greater than 70” yet the formula has greater than 70 for course A instead of greater than 50 and greater than 50 for course B instead of 70.
Thanks for calling this out. It was a miss from my end. I’ve updated it.