What is the Excel AND Function?
Excel AND function used to determine a condition. Return TRUE if ALL is TRUE and return FALSE if ANY false value found.
AND Syntax
AND(logical1, [logical2], ...)
logical1, required, evaluate the first condition
logical2, optional, up to a maximum of 255 conditions
AND function ignores any argument contains text or empty cells. If any argument contains no logical values, then AND function returns the #VALUE! Error.
How to Use AND Function in Excel
AND function with two conditions
Here is an example of using AND function and two conditions to be evaluated.
The result is as shown below
The AND function returns TRUE if ALL conditions are TRUE, return FALSE if ANY FALSE value found, either one or all.
AND function with three conditions
Here is an example of using AND function and three conditions to be evaluated.
The result is as shown below
AND function returns TRUE if all are TRUE. If there is one FALSE condition, AND functions return FALSE, applies to any number of conditions.
AND Example, Have Your Ideal Weight?
Suppose there is data like the image below.
Which of the twelve names above has ideal weight? The value of BMI determines who has the perfect body weight. Calculate the BMI value by dividing the weight (in KG) by the height (in Meter2).
The ideal weight criteria according to BMI is greater or equal to 18.5 AND smaller or equal to 24.9.
There are two conditions to be evaluated. Condition #1 BMI>=18.5 and condition #2 BMI<=24.9. If ALL of the conditions is TRUE, then return TRUE (Ideal weight). So, use AND function to evaluate the conditions.
Here are the steps on how to use AND function.
- Put the cursor in cell E2
- Type the AND formula
- Type for condition #1, D2>=5
- Type for condition #2, D2<=9
- Press the ENTER button
Look the formula below for the result
=AND(D2>=18.5,D2<=24.9)
Do a copy in cell E2 then paste in range E3:E13. The result is as shown below.
There are two names have an ideal weight. All of them met all the conditions.
AND function result is not informative enough, its displays TRUE/FALSE value only. For the more informative result, Use Excel IF Statement.
Another AND Function Alternative
Logical values have a unique characteristic; TRUE converted to numbers 1 and FALSE to number 0 if involved in mathematical operations (add, subtract, multiply, divide).
The simple multiplication formula is another AND function alternatives. The logical AND function will be TRUE if ALL condition is TRUE. The multiplication result will be equal to 1 if all test result is TRUE.
Multiplication formula substitute AND function with two conditions
For example, there are data such as the image below. Could multiplication formulas be used as a substitute for the AND function?
The result is as shown below
The AND function and the multiplication formula results are same.
Multiplication formula substitute AND function with three conditions
There are three conditions, can you use multiplication formula instead of AND function?
The result is as shown below
The AND function and the multiplication formula results still the same.
An Example for AND Function Replacement, Have Your Ideal Weight?
For example, there is data such as the image below, the same data with the previous. Are you able to use multiplication formula instead of AND function to find anyone who had ideal weight?
According to BMI, ideal weight is more than or equal 18.5 AND less than or equal 24.9.
There are two conditions; both must be TRUE. The multiplication formula is used to multiply the condition #1 (checks whether BMI is more than or equal 18.5) and condition #2 (checks whether BMI is less than or equal 24.9).
Here are the steps to use the multiplication formula as AND function replacement.
- Put the cursor in cell F2
- Type an equal sign “=”
- Type for condition #1, D2>=18.5
- Type * (asterisk) sign for the multiplication formula
- Type for condition #2, D2<=24.9
- Check whether the multiplication result is equal to 1
- Press the ENTER key
Look the formula below for the result
=(D2>=18.5)*(D2<=24.9)=1
Do a copy in cell F2 then do a paste in range F3:F13. The result is as shown below.
Compare with AND function result. Are there any differences?
