*81*

# 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))>