How to use Excel IF function with AND, OR Combined with Examples

Hi Friends,

After the discussion about IF() function in Excel I am assuming that you are now quite comfortable with this function to use its' basic properties. So, in this article I'll go little more deeper use of IF() function. Today, I shall discuss about Nested IF with few examples and with some logical function like AND(), OR() etc.

Nested IF with AND or OR function is very useful where you get two or more parameters as input and you have to derive the data based upon the conditions provided, let us take an example, if somebody asks you to prepare a mark sheet for a school and gives you conditions that if any student scores 50% or more in 5 subjects and in aggregate of 60% then only he/she passes else fails. At this time you need to use nested IFs and nested function like AND or OR function.

Nested IF function in Excel

In my last article and of course in the YouTube Videos I mentioned the word Nested repetitively and intentionally I forced you to keep in mind that Nested  Function is a function within a function. Well, that's true, a nested function is a function covered by another function may be the same function or different function(s).

Now, we'll look into the nested property of IF() function and obviously how to use it with simple examples. We'll use the function AND() and the function OR()  in IF() function by the example of a mark sheet of a fictitious school and fictitious students in this article a little and in the upcoming article in details.

Lets' first understand the logical functions (AND & OR).

 Logical AND: A logical AND() function is used to check whether one or more given conditions are TRUE or FALSE. The AND() function returns TRUE if all the supplied arguments or conditions are TRUE, if one condition is FALSE then it will return FALSE. Its very simple.

Syntax

=AND(logical1, logical2, logical3, ...)

Here logical1, logical2, logical3 etc are the conditions to be provided one after another like A2>50 or A2<=100 etc. separated by comma(s).

You can enter upto 255 such logical value within this function.

How AND() function works ?

Let suppose in a given table of a mark sheet two numbers for two subjects are given in A2 and A3 as 79 and 120 respectively and given a condition to check whether both these numbers  are greater than 50 or not.

From the syntax of AND() function, if we write the formula as =AND(A2>50, A3>50), the output would be TRUE because both these numbers are greater than 50. Now suppose A2 is 45 then the output of this function would be FALSE because one of these arguments are false.

AND() function returns TRUE when all the given conditions are TRUE and if any one of these conditions is false it returns FALSE (Boolean) logical output.

In the above small example I want to convey you that if any one of the parameter or argument provided to AND() function is FALSE then the whole formula returns FALSE and if all the arguments are TRUE then this function returns TRUE. This is the way how AND function works in Excel.

Example of AND function

Now another useful function is OR(). Lets' find how this function works.

Syntax

=OR(logical1, logical2, logical3, ...)

Like AND function logical1, logical2, logical3 etc are the conditions to be provided one after another seperated by comma like A2>50, A2<=100 etc. And like AND function you can enter 1 to 255 logical tests within this formula.

How OR() function works ?

If we take the same example of marks for two subjects as 79 and 120 and make a little change in the condition as that if any of the number is greater than 100 then that student is qualified to another class.

Now using OR() function the formula now becomes =OR(A2>100,A3>100) when we press enter the output would be TRUE because one condition is satisfied but to see the effect of this function when all conditions are false let us change if the marks of A3 from 100 to 90 and rewrite the formula then this formula will return FALSE because all the given conditions are FALSE.

OR() function returns TRUE when any one of the given conditions is TRUE and if all the conditions is false it returns FALSE (Boolean) logical output.

We'll  use AND() function when the output needs all the given conditions to be TRUE and we'll use OR() function while the ouput needs only one condition to be satisfied.

I know at first it looks a little bit confusing but the below images  with example will make this very easy to understand.

Example of OR function

Let us take an example of a mark sheet. If we suppose that if any of the student in the below mark sheet has got below 45 marks in any subject then he/she fails, in this situation, we'll use the AND formula and it would be like

 =AND(D3>45,E3>45)  - for first student, output is TRUE because marks are greater than 45 in each subject

=AND(D4>45,E4>45)   - for second student, output is FALSE because Subject 2 marks is less than 45.

AND function Expanded

Now if we give condition that any student who achieved 45 marks or above in any subject will pass, then we have to use OR function and this formula becomes,

=OR(D3>45,E3>45) - for first student, output is TRUE as both subject's marks are greater than 45.

=OR(D4>45,E4>45) - for second student, output is TRUE because one of the subject's marks is greater than 45 although marks obtained in subject 2 is less than 45.

AND and OR function

So, this is how AND & OR function works.

How to use nested IF and AND function

To understand this we'll take the help of one example, let suppose that in the mark sheet table, if any student has got below 45 then he/she fails, otherwise he/she passes and we have to print the word Pass or Fail; then our formula becomes with IF and AND function combined or nested,

=IF(AND(D3>45,E3>45),"Pass","Fail").

Now recall my last article about the syntax of IF() function,

=IF(logical_test, value_if_true, value_if_false) or we can write theoretically =IF(Conditional test, value_if_true, value_if_false). Here AND(D3>45,E3>45) is my conditional test parameter with the IF function.

So, whenever this AND function returns TRUE, the value_if_true part of IF function gets printed, otherwise the value_if_false part is printed like the second student's example, =IF(AND(D4>45,E4>45),"Pass","Fail")

Graphically the use of AND  and IF function together is shown in the below image.

Working of IF and AND function in Excel

Similarly you can use OR function in place of AND function if such conditions are provided.

You can watch YouTube video clicking here or in below box. 

Hope this part was very easy for you, in my next article I'll show you some complex examples related to Nested IF function and other few important functions in excel with their formula, till then keep reading and keep watching my YouTube Videos.

Thank you...