Combined Use of Excel Logical Functions IF, OR, AND

My last office related article on this site was on 27th November. Today it is 27th December that means it is just after one moth I am writing an article on office tips. The similarity in date is only a coincidence though. As I already mentioned on this site earlier, I want to write something distinctive when I write office tips. The main reason is that Microsoft has already a good supporting site where they have well covered many aspects of Excel. I made a basic Google search before posting this article and could not find any tutorial on combined use of excel logical functions anywhere. Actually I have written this office tip few years back for an e-book that I have published on one of my experimental blog, that shutdown due to some reason.

IF, AND, OR are the main logical functions in Excel. It gives you awesome results when you use it together ‘logically’. For example in a stock statement you can easily ensure that your products are not reaching the threshold limit that you set.

For example just assume your threshold limit of a particular product is 10. If any product fall below this limit you can easily find this if you use IF logical function. To use other two logical functions in a useful way you must know how to use IF logical function first. So take time to well understand the below example first.

Type this formula in any cell other than A1

=IF(A1<10, “stock fall below threshold limit”,”OK”)

Now input value 5 into cell A1

You can see the formula result as “stock fall below threshold limit”

Again check the result by imputing value 100

See the result “OK”

If you understand this logic other things are just simple. I am not detailing the use of AND, OR logical functions. The following example will be enough for you to understand these two functions along with the combined use of excel logical functions. But try to understand that in order to clearly understand the below logical functions you must thorough the IF function, above, first.

Hope you have done your home work. Now let us start.

As an example we can take the marks scored by a student in three subjects in an exam. We have to find, using logical functions, whether he is passed or not based on a given condition.

I am using three different conditions to make logical functions clearer to you.

* First check the screenshot and enter the value in a blank Spreadsheet.

Now about the three conditions that determine whether the student is passed or not in this example.

1. Passed if marks scored in any subject is >49
2. Passed only if marks scored in all subjects is >49
3. Passed if marks scored in any two subjects is >49

Now using logical functions you can find the result. See how?

AND : Given true if all conditions are true. That means you can use with point no. 2. If the student scored more than 49 marks in all subjects, he is considered as passed.

OR : Given true if any of the conditions you specified is true. You can use this function with point no. 1. If the student scored more than 49 marks in any one subject out of three, he is considered as passed.

Now the third condition is important, i.e. point no. 3 above. Here you need to combine and use AND, OR logical functions.

In all three case I use IF logical functions. I am not going to confuse you. That is why I already told you to be familiar with IF logical function first.

Now see the formula structure

Used with point no. 2 in Cell E3 : =IF(OR(B3>49,C3<49,D3>49),”Passed”,”Failed”)

Used with point no. 1 in Cell F3 : =IF(AND(B3>49,C3>49,D3>49),”Passed”,”Failed”)

Used with point no. 3 in Cell G3 : =IF(OR(AND(B3>49,C3>49),AND(B3>49,D3>49),AND(C3>49,D3>49)),”Won”,”Failed”)

* Just apply the formula against the already entered data.

It will only clearer to you, if you apply the data and formula in a blank Spreadsheet as mentioned in the tutorial.  If you face any problem leave your comment below.