In this tutorial, we will learn about the AND function, and how it is used with the IF function. The AND function only returns the value True or False, but with the IF function it can return the value of our choice. AND function will be nested in IF function. Microsoft Excel 2021 is used.
AND function is better used with IF function, so that the return value can have different text value other than True or False.
We will be using IF and AND function for this tutorial, following are the links to their tutorials:
The IF function, checks the if the given logical condition is true or false and returns a value based on the result. The AND function returns true, if all the conditions are true, if one or more conditions are false, it returns false.
For this tutorial, we will be using an example of the Students Marksheet and decide whether a student has passed or failed the exam. In order to pass the exam, the student should have attained equal to or more than 50 marks in all of the subjects to pass.
We will be using following data.
The result will be entered in the Result column. We will use AND function in the argument of the IF function.
Using IF function
IF function has three arguments, logical condition, value if true, value if false.
Syntax: IF(logical condition, value if true, value if false).
First, we will enter the IF function, so enter, =IF(
For the IF function, first we enter the logical condition. So, for the Logical condition, we will enter AND function.
Using AND function
AND function checks if all the provided conditions are true, then returns a value true or false.
Syntax: AND( logical condition1, logical condition2...)
So, the formula becomes: =IF(AND(
The conditions are that the students should have equal to or more than 50 marks in all the subjects to pass.
Enter the cell position by clicking on the cell or it can be entered manually.
So, the formula becomes: =IF(AND(B3>=50, while B3 is the position of the cell.
Put comma , and apply this condition for all the other subjects or columns.
So, the formula becomes: =IF(AND(B3>-50,C3>=50,D3>=), B3, C3 and D3 are cell positions.
Close the parenthesis after entering all the conditions of the AND function.
True and False value for IF function
Now, we enter the value returns for if AND function. returns true, and the value for if AND function returns false.
After the AND function is complete, put comma, and enter the value if the result is true, and then after one more comma, enter the value if result is false.
So, the formula becomes: =IF(AND(B3>-50,C3>=50,D3>=),"Pass", "Fail")
Press Enter to apply the formula.
To apply the formula along the column, select the cell, drag the bottom right corner of the cell along the column to apply formula to the other cells of the column.
