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:

  1. IF
  2. AND
  3. 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

  1. Create the data as shown belowIF AND OR Functions 1
  2. 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”))

  1. Note that we have used a nested IF function along with the usage of AND conditionIF AND OR Functions 2
  2. 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
  3. 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
  4. Drag the formula down till D11. Cross check your results with the screenshot below:IF AND OR Functions 3

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

  1. Create the data as shown belowIF AND OR Functions 4
  2. 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”)

  1. We have used an OR condition in the logical test part of the IF statementIF AND OR Functions 5
  2. The OR condition returns TRUE either if the Lab Score is greater than 50 or if the Project Score is greater than 50
  3. When the result of the OR is TRUE, the result of the IF statement will be a “Pass”. Otherwise, it results in “Fail”
  4. Drag the formula down till D11. Cross check your results with the screenshot below:IF AND OR Functions 6

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:

Download Excel file

Please share

Did you find this useful?  Why not share the love and tell your friends on the different social media platforms.

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to ConvertKit ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Abdelrahman Abdou
Latest posts by Abdelrahman Abdou (see all)
It's only fair to share...Share on Facebook
Facebook
0Pin on Pinterest
Pinterest
0Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin