AND Function

Determines if the given conditions in a test are TRUE

Over 2 million + professionals use CFI to learn accounting, financial analysis, modeling and more. Unlock the essentials of corporate finance with our free resources and get an exclusive sneak peek at the first module of each course. Start Free

What is the AND Function?

The AND Function[1] is categorized under Excel Logical functions. It is used to determine if the given conditions in a test are TRUE. For example, we can use the function to test if a number in cell B1 is greater than 50 and less than 100.

As a financial analyst, the function is useful in testing multiple conditions, specifically, if multiple conditions are met to make sure they all are true. Also, it helps us avoid extra nested IFS, and the AND function can be combined with the OR function.

Formula

=AND(logical1, [logical2], …)

The AND function uses the following arguments:

  1. Logical1 (required argument) – This is the first condition or logical value to be evaluated.
  2. Logical2 (optional requirement) – This is the second condition or logical value to be evaluated.

Notes:

  1. We can set up to 255 conditions for the AND function.
  2. The function tests a number of supplied conditions and returns:
    1. TRUE if ALL of the conditions evaluate to TRUE; or
    2. FALSE otherwise (i.e., if ANY of the conditions evaluate to FALSE).

How to Use the AND Function in Excel?

To understand the uses of the AND function, let us consider a few examples:

Example 1

Suppose we wish to calculate the bonus for every salesperson in our company. To be eligible for a 5% bonus, the salesperson should have achieved sales higher than $5,000 in a year. Otherwise, they should have achieved an account goal of 4 accounts or higher. To earn a bonus of 15%, they should have achieved both conditions together.

We are given the data given below:

AND Function

 To calculate the bonus commission, we will use the AND function. The formula will be:

 

AND Function - Example 1

 

The formula for calculating the bonus does not require the AND function but we need to use the OR function. The formula to use will be:

AND Function - Example 1a

The resulting table will be similar to the one below:

AND Function - Example 1b

Example 2

Let’s see how we can use the AND function to test if a numeric value falls between two specific numbers. For this, we can use the AND function with two logical tests.

Suppose we are given the data below:

AND Function - Example 2

The formula to use is:

AND Function - Example 2a

In the above formula, the value is compared to the smaller of the two numbers, determined by the MIN function. In the second expression, the value is compared to the larger of the two numbers, determined by the MAX function. The AND function will return TRUE only when the value is greater than the smaller number and less than the larger number.

We get the results below:

AND Function - Example 2b

Things to Remember about the AND Function

  1. VALUE! error – Occurs when no logical values are found or created during the evaluation.
  2. Test values or empty cells provided as arguments are ignored by the AND function.

Click here to download the sample Excel file

Additional Resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

Article Sources

  1. AND Function

Excel Tutorial

To master the art of Excel, check out CFI’s Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

Launch CFI’s Excel Course now

to take your career to the next level and move up the ladder!

0 search results for ‘