Functions IF, AND, OR in Excel

In this example, I show you how to use IF, AND, OR functions together in the Excel worksheet. There is not only the one solution in logical tasks. You can always modify the formula in different ways.

Example

There are two exams in the table. Each student has to pass both of exams. Status is OK means that he passed. Status NOK means he did not pass.

Table with logical problem

Task: Create a function that evaluate if both exams are OK. If it’s true then Result is Y. If it’s not true then Result is N.

Solution by using functions IF, AND

We will focus on the first student and the result Y. When it occurs? The answer is: When the cells B2 and C2 are both set to OK. So, we create the function like this:

=IF(AND(B2=”OK”,C2=”OK”),”Y”,”N”)

functions IF AND

The parameters OK, Y and N are text strings so they have to be in the quotation marks.

Function AND returns only TRUE/FALSE value.
– If all of the logical tests in the AND function are TRUE, the result is TRUE.
– If at least one of the logical tests is FALSE, the result is FALSE.

Solution by using functions IF, OR

Now, we examine the result N. It occurs when the cell B2 or C2 is set to NOK. The formula will be:

=IF(OR(B2=”NOK”,C2=”NOK”),”N”,”Y”)

functions IF OR

Notice that the combination IF with AND is a logical opposite of the function IF with OR. Reverse is also true, OR is a logical opposite of AND.

Solution by using multiple IF

This is a little bit different way to solve the logical problem. We put one IF function into the other.

=IF(B2=”OK”,IF(C2=”OK”,”Y”,”N”),”N”)

multiple IF functions

The first IF function will evaluate B2=”OK”. If it’s FALSE then the result is N. If it’s TRUE we need the other IF function to evaluate C2=”OK”. If it’s TRUE the result is Y, if not then the result is N.

You can change this formula to check B2=”NOK”. The principle is the same but everything is inverted.

=IF(B2=”NOK”,”N”,IF(C2=”NOK”,”N”,”Y”))

multiple IF funtions2

Result

You can go by different ways but the result will be always the same and correct.

result

Advantages/disadvantages of using AND, OR

– The formula is shorter (especially when you need to test more conditions).
– Sometimes, it is very hard to decide whether to use AND or OR.

Advantages/disadvantages of using multiple IF

– The formula is longer (especially when you need to test more conditions).
– In more complex task is easier to figure out the formula.

Useful tool – Evaluate Formula

This tool can help you to better understand the formula and find errors. Go to the menu Formulas >> Evaluate Formula.

Good luck and be patient. Logical formulas sometimes need more time to think through.

Add a comment »4 comments to this article

  1. Overview of all possible conditions in IF formula. Is there such?

    Reply

  2. Do you have an example with NOT function? I need to reverse the logic in the IF formula. The case is very similar as here. Table is scoreboard. The task is to select students who failed in exam.

    Reply

  3. 72ENVD

    Reply

  4. dsjkfj

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress