How to use AND function in Excel– Formula, Example

AND function is one of the logical functions in Microsoft excel that is used to determine the condition provided by the user is True or False. The And function requires a minimum of 1 condition (and the maximum conditions in And function can go up to 255) for example the test such as looking for a number that is in cell A1 is greater than 10 but less than 15. [=AND(A1>10,A1<15)]

If the numbers fall in the conditions, the result we receive is True, else False.

The And function is very helpful when it comes to determining multiple conditions being true or not. And function can be combined with IF function, to provide dynamic outcome, which will also be covered in the latter topic.

 The formula of AND Function

=AND(Logical1, [Logical2],..)

The AND function logical arguments can be further described as –

Logical 1 – The first logic/condition to be evaluated which is true or false

Logical 2 – The Second logic/condition to be evaluated.

Remark –

•  If all the logic satisfies the condition, the result is True.
• If even a single logic test does not satisfy the condition, the result we receive is false.
•  If the cell or the array is empty, the values are ignored.
• #Value! Error in end function is observed when the logical range does not contain any logical value.

 How to use AND Function – Example

Example – 1

We have two numbers 10 & 15, we have to put the condition that the number we have falls between the given two i.e., 10 & 15, then we can use AND function such as

 

in the above example, the result we receive is False since the value is 20.

Once the value is changed to 12 the result is observed to be True.

 

Combining the AND function with Max and MIN function 

Suppose we have the given data as seen

Consider we have 3 different series, and we have to find out that the Series 1 values fall between series 2 & series 3, there we can combine MAX/Min function with AND Function

If you observe the formula tab, D4 is the series we want to check that it falls between Series 2 & Series 3, so we combined the max and minimum function to get the result dynamically.

Logic 1- Denotes 43(D4) is greater than the minimum of 39 & 83

Logic 2 - Denotes 43(D4) is less than the maximum of 39 & 83

Since 43 falls between 39 & 83, the result we receive from the Combined AND(max/min) function is True.

Since 69 doesn't fall between 72 & 74, the result we receive is false.

Example – 2 Combination of AND Function and IF Function 

Suppose there are 3 students in class if they score more than 40 marks in Maths, Physics & chemistry. They pass the exam and get 5 marks extra in the least scoring subject.

We have used IF function to provide +5 marks to the least scoring subject of the student who passes the exam, And function provides the Pass and Fail criteria by analyzing the data of the three subjects to be above 40 and the minimum function adds +5 marks to the least scoring subject.