Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)®. Register today!

AND Function

Determines if the given conditions in a test are TRUE

What is the AND Function?

The AND function is categorized under 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 100 and less than 50.

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 can be combined with the OR function.

 

Formula

=AND(logical1, [logical2], …)

 

The AND function uses the following arguments:

  1. Logical1 (required argument) – It is the first condition or logical value to be evaluated.
  2. Logical2 (optional requirement) – It 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 resources:

  • Advanced Excel Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for Windows and Mac
  • Financial Modeling Program

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE 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 Free Excel Course now to take your career to the next level and move up the ladder!