In the last article you learnt, how to use the IF and AND functions. Today we shall learn some more logical functions using multiple conditions. This article includes:
- Using OR function
- Using IF and OR function
- Using AND and OR function
- Using IF, AND and OR function
- Using NestedIf functions
OR function
OR function returns the value as TRUE if any of the condition is met and FALSE if all the conditions are not met.
Syntax of OR function
OR(logical1,logical2,…) wherein
Logical1,logical2,..: conditions you want to test that can be either TRUE or FALSE. You can add upto 255 conditions.
Using OR function
In the example below, we want to know if the student has passed in atleast 1 subject so we use OR function. Since the passing marks is 35 our logical 1 is B2>=35 and logical 2 is C2>=35.
When you press enter and drag the fill handle you will notice that even if a student has passed in one subject, the output is TRUE, only column D6 displays False as both the conditions are not met.
Using IF and OR function
Now, if you want Pass or Fail instead of True or False then you will have to combine IF function with OR. So in that case our formula starts with a IF function and in that we incorporate the OR function (See image below).
When you press enter and drag the fill handle, you will see that now the TRUE or FALSE has been replaced with Pass and Fail.
Using AND or OR function
When you have multiple conditions to evaluate, there are chances that you use the AND and OR function together. In our example below, we have 2 subjects consisting of 2 tests each. If the student has cleared either Test 1 in both the subjects or Test 2 in both the subjects, he is considered as pass and therefore we combine the OR and AND formula to evaluate this. So our conditions are
1. B3>=35,D3>=35
2. C3>=35,E3>=35
After pressing enter and dragging the fill handle, you will get your desired results in the form of TRUE and FALSE wherein true indicates Pass and False indicates Fail.
Using IF, AND and OR function
In the above case, if you want to add the arguments (Pass or Fail) then you will have to use the IF condition along with OR and AND. In that case our formula becomes =IF(OR(AND(B3>=35,D3>=35),AND(C3>=35,E3>=35)),”Pass”,”Fail”)
Make sure you close all the open brackets and then press enter and drag the fill handle and you shall receive your desired results.
Using NestedIf functions
When you use multiple if conditions to evaluate the data then it is known as Nestedif function. The Nestedif functions are very useful when you want your formula to return 3 or more values.
In our example below we shall grade the students as follows
If Total is more than 200 – A grade
150 – 200 – B grade
100 – 150 – C grade
Below 100 – D grade
Press enter and drag the fill handle and you will see your grades as per your own conditions.
We hope that this tutorial was helpful to you. If you have any doubts or suggestions mention it in our comments box and we shall get back to you as early as possible.
Related links you will like:
Comments