Share with your network!

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.

Formula for OR function in Excel

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 OR function in Excel

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).

Using IF condition along with OR in excel

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 IF condition along with OR in excel

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

Combining OR and AND function Excel

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”)

Combining IF, OR and AND functions in Excel

Make sure you close all the open brackets and then press enter and drag the fill handle and you shall receive your desired results.

Combining IF, OR and AND functions in Excel

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

Nestedif formula in excel

Press enter and drag the fill handle and you will see your grades as per your own conditions.

Using Nestedif formula in Excel

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.