AND Function in Excel
What is AND Function?
In Excel, the AND function is a logical function that tests for a number of conditions and returns “true” or “false” based on whether the conditions are met or not. We used the AND function to see if a set of conditions in a test are TRUE. For example, the AND function can be used to test if a number in cell A2 is greater than 100 and less than 200. The AND function’s purpose is to test multiple conditions.
We can use the AND function as the logical test inside the IF function to avoid additional nested Ifs, and it can also be combined with the OR function.
The AND function is used to verify multiple logical conditions at once, up to 255 conditions, which are supplied as arguments. Each argument (logical1, logical2, etc.) must be an expression that returns TRUE or FALSE or a value that can be evaluated as TRUE or FALSE. Constants, arrays, cell references, or logical expressions can be the arguments provided to the AND function.
Syntax of the AND Function
The following is the syntax of the AND function:
Arguments of the AND Functions
The following are the two arguments of the AND function:
- Logical 1: Logical 1 is the first logical value or condition to evaluate.
- Logical 2: Logical 2 is the second logical value or condition to evaluate.
The logical 1 argument is required, but the logical 2 argument is optional.
Characteristics of the AND Function
The AND Function has the following characteristics:
- The AND function returns “TRUE” if all logical values or conditions evaluate to true.
- The AND function returns “FALSE” if any of the logical values or conditions evaluate to false.
- The AND function disregards empty cells as an argument.
- It is frequently combined with other Excel functions such as IF, OR, and so on.
- The AND function can have more logical values depending on the condition and requirement.
- When evaluating numbers, it considers zero to be “FALSE” and non -zero values to be “TRUE.”
The Output of AND Function
The following are the outputs of the AND function in various situations:
| FORMULA | OUTPUT |
|---|---|
| =AND(TRUE,TRUE) | TRUE |
| =AND(TRUE,FALSE) | FALSE |
| =AND(FALSE,FALSE) | FALSE |
| =AND(FALSE,TRUE) | FALSE |
How to Use AND Function in Excel?
The AND function is straightforward to use. Let’s have a look at a few examples to understand how it works.
Example 1: – AND Function
In this example, there is a game where game has four levels and 12 players. A player must complete all four levels to win. If the player fails to complete any of four levels, he or she is eliminated from the game.
The player’s performance at different levels is summarized in the table below. We have to decide who the winner is.
Now, we apply the AND formula in column F.
The formula is:
Player D, E, I, and K have completed all the levels. The AND function returns “true” since all of the logical conditions for these four players are met.
The remaining players failed to complete all four stages. The AND function returns “false” if any of the levels are not cleared.
Example 1.1 AND Function
We’ll use the AND function in this example to see if a numeric value is between two defined values. We may accomplish this by combining two logical tests with the AND function.
Suppose we have the following data set:
We will use the below formula:
In the above formula, the MIN function is used to compare the value to the smaller of the two numbers. In the second expression, the value is compared to the greater of the two values, which is determined by the MAX function. The AND function will return TRUE if the value is greater than the smaller number and lesser than the larger number.
We get the output below:
Example 1.2: AND Function
Let’s explore more about AND function with the help of this example. Suppose we want to figure out the bonus for each salesperson in our company. To be eligible for the 5% bonus, the seller must have achieved sales of more than $5,000 in one year. Otherwise, they should have met or exceeded their account objective of 5 accounts. They needed to meet both criteria in order to receive a 15% bonus.
We have the below data:
We’ll use the AND function to determine the bonus commission. The formula will be as follows:
After applying this formula, the output will be:
We don’t need the AND function to calculate the bonus, but we’ll need to use the OR function. The following is the formula to use:
After applying the formula, the result is:
Example 2: – AND Function with Nested IF Function
In this example, we have the marks of 12 students in a school. We need to determine each student’s grade based on the criteria given.
A student gets “A+” if he/she scores more than 90% marks. A Grade “A” is given if the percentage is greater than or equal to 80% but less than or equal to 90%, a grade.
The students fail if the percentage is less than 40%. The grades for the various percentage are also listed in the table below.
We apply the below formula:
If we use the nested IF function with numerous AND functions to calculate the grades. The later enables the simultaneous testing of two conditions.
The following is the syntax for the IF function:
The IF function return “true” if the condition is met, otherwise “false.”
“B2>90” is the initial logical test or condition. The grade “A+” is assigned if this condition is “true”. If the condition is “false” then the IF function evaluates the next condition.
“B2<=90, B2>80 is the next logical test or condition. If this condition is “true,” grade “A” is allotted. If this condition is “false” then the next statement is evaluated. Similarly, the IF function checks each condition in the formula.
“B2=50, B2>40” is the final logical test. If this condition is “true” the student is assigned a grade “D”, but if it is not, the student will fail.
As illustrated in the below image, we use the formula for all categories of students.
Example 3: Nested AND Function
In this example, we will understand the concept of nested AND function. In this example, we have a list of people who want to join the army if certain requirements are met. The following are the eligibility requirements:
- It is must that the candidate’s height must be greater than or equal to 18, but less than 35 years.
- The candidate’s eyesight must be
- The candidate’s height must be greater than 167 cm.
- The candidate must have completed the long-run task.
We have to determine which prospects are eligible to join the Army.
We utilize the nested AND function in order to evaluate the candidates based on the specified parameters.
The following formula is used.
We evaluate multiple logical conditions at the same time. We also look to see if the age is within the prescribed range. As a result, we use the AND function within another AND function.
The formula’s result is depicted in the following figure.
Anurag and Trishant are the only candidates who meet the selection requirements or conditions. As a result, the output of their eligibility (column F) is “true.” The rest of the candidates are ineligible to join the army.
Limitations of AND Function
The following are the limitations of AND function:
- Since Excel 2007, the AND function can check for 255 arguments if the formula’s length is less than 8,162
- The AND function in Excel 2003 and previous versions can test up to 30 arguments if the formula’s length is less than 10,24
- The AND function returns “#VALUE! Error” if logical conditions are passes as text or if none of the arguments evaluates to a logical value.
- “#VALUE! Error” is returned by the AND function if all of the arguments provided are empty cells.
The following two images demonstrate the results of the AND function if an empty cell and a text string are provided as an argument.
(a2,b2))>
(a2,b2))>
