AND Function in Excel

AND Function in Excel

AND function in Excel is categorized as a logical function; it returns two values only that are TRUE and FALSE. This AND in excel tests the condition specified and returns TRUE, if conditions are met as TRUE, else it returns FALSE. This function is often used with other Excel functions, AND in Excel can significantly broaden the abilities of the worksheet.

And Formula in Excel

AND formula is written as

AND( condition1, [condition2],…)

Explanation of AND Function in Excel

logical 1 is the first condition or the logical value to evaluate

logical 2 is the optional argument, is the second condition or logical value to evaluate

There can be more logical expressions (conditions) to test depending on the situations and requirements.

AND function in Excel returns TRUE if all conditions are TRUE. It returns FALSE if any of the condition is FALSE.

If all the logical expressions evaluate to TRUE (first case), the AND function in excel will return TRUE. If any of the logical expression evaluates to FALSE (second, third, and fourth case), the AND in excel will return FALSE.

If the logical expression argument evaluates numbers, instead of Boolean logical value TRUE/FALSE, the value zero is treated as FALSE and all non-zero value as TRUE.

And a function in excel is often used with functions like IF, OR, and other functions.

How to Use AND Function in Excel

AND Function in Excel is straightforward and easy to use. Let’s understand the working of AND Function in excel with examples.

AND in Excel Example #1

There are ten players, and each has to clear all 3 levels to be a winner; that is, if the player clears level 1, level 2, and level 3, he/she will be a winner else if fails to clears any of the levels he/she will fail and will not be declared as a winner.

We will use the AND function on excel to check if any of the levels is not clear. He/she is not a winner then. So, applying the AND Formula in E column. 

Output:

Player 2 and player 10 have cleared all the levels. Hence all logical conditions are TRUE. Therefore, the AND Excel functions gave the output TRUE. For the rest, if any level out of all three was not cleared, the logical expression evaluated as FALSE, and the AND in excel resulted in FALSE output.

AND in Excel Example #2

Using AND Excel function with IF function

There is a list of students with their percentages, and we want to find out the grades, where the grade criteria are shown below in the table:

If the percentage is greater than 90%, then the grade will be A+ when the percentage is greater than or equal to 80% and less than 90%, then the grade will be A, and similarly, other Grades based on percentage and if a student is Fail the percentage is less than 40%. 

The formula that we will use is

=IF(B2>90,”A+”,IF(AND(B2<=90,B2>80),”A”,IF(AND(B2<=80,B2>75),”B+”,IF(AND(B2<=75,B2>70),”B”,IF(AND(B2<=70,B2>60),”C+”,IF(AND(B2<=60,B2>50),”C”,IF(AND(B2<=50,B2>40),”D”,”FAIL”)))))))

We have used Nested IF’s in excel with multiple AND excel functions to compute the Grade of the student.

IF formula tests the condition and gives the output as TRUE if the condition matches else return FALSE.

=if(logical_test, [value_if_TRUE],[value_if_FALSE])

The first logical test, in this case, is B2>90, if this is TRUE, then the grade is ‘A+’ if this condition is not TRUE if checks for the statement after the comma and it again entered another if condition, in which we have to test 2 conditions that are if the percentage is greater than 80 and less than or equal to 90 so, logical statements are

B2<=90, B2>80

Since we have to test two conditions together, we have used the AND Excel function. If B2<=90 and B2>80, is TRUE, the grade will be ‘A,’ if this condition is not TRUE, IF checks for the statement after the comma, and it again enters another IF condition and will continue to check each statement till the last bracket closes. The last IF statement checks, percentage <=50, and percentage >40, hence the last IF statement evaluates Grade ‘D’ else evaluate ‘FAIL.’

AND in Excel Example #3

Using AND excel function with IF function

There is a list of employees and the sale amount in Column B; on the basis of their sale amount, the incentives are allotted to each employee. Sale incentive criteria are

We need to calculate the incentive of each employee based on his performance for cracking the sale base on the criteria mentioned above in the table

Roman made a sale of $3000, he will receive an incentive amount of $400, and David and Tom were not able to even crack $1000 sale, the bare minimum sale criteria for an incentive. Hence they will not get any incentive.

Formula that we will use is

=IF(AND(B2>=3000),400,IF(AND(B2>=2000,B2<3000),200,IF(AND(B2>=1500,B2<2000),150,IF(AND(B2>=1000,B2<1500),100,0))))

We have again used Nested IF in excel conditions with multiple AND excel function testing the all the logical conditions together in the formula above to compute the sale incentive of the employee based on the sale amount that he/she made.

David and Tom were not able to meet the incentive criteria; hence their incentive amount is $0.

Nesting of AND Function in Excel

Nesting functions in Excel refer to using one function inside another function. Excel allows you to nest up to 64 levels of functions.

AND in Excel Example #4

There is a list of candidates who appeared for posting in the Army for certain selection conditions.

Eligibility Criteria: Age has to be greater than or equal to 18, but less than 35 years with a height greater than 167 cms, eyesight has to be normal and has completed the long-run task

Here, we will be using the Nested AND in Excel to check the eligibility criteria.

Formula that we will use is

=AND(B2=”Normal”,C2>167,D2=”Successful”,AND(E2>=18,E2<35))

As you can see we have, used multiple logical conditions to check if they evaluate to be TRUE, and for age, we have again used the AND function in Excel to check if age is greater than or equal to 18 and less than 35 years.

We have used the AND function in excel inside the AND function to check the age criteria.

Output:

Three candidates passed all the selection criteria, Ralph, Alex, and Scott. Hence, their eligibility computes out as TRUE using the AND function in Excel and for the rest of the candidates, FALSE. 

Limitation of AND Function in Excel

The AND function in Excel can test multiple conditions, not exceeding more than 255 conditions. In Excel version 2003, the AND in excel could test up to 30 arguments, but in Excel version 2007 and later, it can handle 255 arguments.

The AND function in excel returns #Value! Error if logical conditions are passed as a text or string.

Recommended Articles

This has been a guide to AND Function in Excel. Here we discuss the AND Formula in Excel and how to use AND Function in Excel along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –

• Excel Pivot Table Add Column
• Excel Convert Function
• VBA Boolean Data Type
• IF AND Formula in Excel