MS Excel: AND Function (WS)
The Microsoft Excel AND function returns TRUE if all conditions are TRUE. It returns FALSE if any of the conditions are FALSE. Please note that the VBA version of the AND function has different syntax.
AND Function (Syntax)
The syntax for the Microsoft Excel AND function is:
AND( condition1, [condition2], ... )
condition is something that you want to test that can either be TRUE or FALSE. There can be up to 30 conditions.
The AND function can be used in the following versions of Microsoft Excel:
- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Type of Excel Function
The AND function can be used in Microsoft Excel as:
- Worksheet function (WS)
AND Examples (as Worksheet Function)
Let's look at some Excel AND function examples and explore how you would use the AND function as a worksheet function in Microsoft Excel:
Based on the Excel spreadsheet above, the following AND function examples would return:
|=AND(A1>10, A1<40)||would return TRUE|
|=AND(A1=30, A2="www.checkyourmath.com")||would return FALSE|
|=AND(A1>=5, A1<=30, A2="www.techonthenet.com")||would return TRUE|
Frequently Asked Questions (AND Function)
Question: I need to translate some Quattro Pro functions to Excel. The #AND# function in Qpro can be placed in the middle of a nest and return a number. For example, @if(.......A1>B1#and#A1<B3,7,0)
What this says is, after some other function, if A1 is greater than B1 and A1 is less than B3, return 7 otherwise 0. How do I get Excel to do this?
Answer: This can be done in Excel by combining the AND function with the IF function like this:
Question: In Microsoft Excel, I'm trying to use the If function to return 25 if cell A1 > 100 and cell B1 < 200. Otherwise, it should return 0.
Answer: You can use the AND function to perform an AND condition in the If function as follows:
In this example, the formula will return 25 if cell A1 is greater than 100 and cell B1 is less than 200. Otherwise, it will return 0.
Question: In Microsoft Excel, I want to write a formula for the following logic:
If R1 AND R2 <0.3 AND R3<0.42 THEN "OK" OTHERWISE "NOT OK"
Answer: You can write an IF statement that uses the AND function as follows:
Question: I have been looking at your Excel IF, AND and OR sections and found this very helpful, however I cannot find the right way to write a formula to express if C2 is either 1,2,3,4,5,6,7,8,9 and F2 is F and F3 is either D,F,B,L,R,C then give a value of 1 if not then 0. I have tried many formulas but just can't get it right, can you help please?