Logical
AND
Definition
AND returns TRUE when every test you give it is true, and FALSE otherwise.
Syntax
=AND(logical1, [logical2], ...)
Arguments
| Argument | Meaning |
|---|---|
logical1 |
The first test, such as A2>=8. |
logical2, ... |
Optional extra tests. Every one must be true for AND to return TRUE. |
Worked example
A kiln run is marked OK only when it lasts at least 8 hours and has 0 faults. Hours are in A2:A4 and faults are in B2:B4.
| A: Hours | B: Faults |
|---|---|
| 6 | 1 |
| 9 | 0 |
| 8 | 2 |
Formula: =IF(AND(A2>=8,B2=0),"OK","Check")
Result: Check. Six hours is below 8, so AND is false and IF returns Check. Filled down, the formula gives Check, OK, and Check.
Practice
Cell A2 holds 12 and cell B2 holds 3. In C2, write a formula that returns 1 when A2 is greater than 10 and B2 is less than 5, and 0 otherwise.
- In C2, write an IF formula that uses AND and shows 1 when A2 is greater than 10 and B2 is less than 5, and 0 otherwise.
- C2 should show the result for 12 and 3.
Related functions
AND is usually placed inside IF so the cell can show one result when every test passes and a different result when any test fails.
Common mistake
Writing =AND(A2>=8,B2=0) when the sheet needs OK or Check: AND returns TRUE or FALSE, so wrap it in IF to convert those values.