Excel track · Exercise 24 of 86 Paid
AND, OR and NOT: combining tests
Combine several tests into one answer, and choose AND or OR to match the question.
Advanced
Stock list with a total row and a smallest item
Read-only data · screen 1 of 2 · answer in B9
In B9, answer TRUE only when the total is over 100 and every item has at least 15 units.
| A | B | |
|---|---|---|
| 1 | Item | Units |
| 2 | Bolt | 18 |
| 3 | Nut | 22 |
| 4 | Washer | 25 |
| 5 | Screw | 30 |
| 6 | Clip | 24 |
| 7 | Pin | 31 |
| 8 | Total | 150 |
| 9 | Both tests |
In B9, answer TRUE when any single item has fewer than 20 units or the total is over 200.
| A | B | |
|---|---|---|
| 1 | Item | Units |
| 2 | Bolt | 18 |
| 3 | Nut | 22 |
| 4 | Washer | 25 |
| 5 | Screw | 30 |
| 6 | Clip | 24 |
| 7 | Pin | 31 |
| 8 | Total | 150 |
| 9 | Either test |
Reference answer
What it does
AND, OR and NOT take logical tests and return a single TRUE or FALSE. AND needs every argument to hold and OR needs one, while NOT flips the value it is given. Each argument is normally a comparison, though a bare number counts as TRUE unless it is zero.
On this sheet the total is 150 and the smallest item is 18. Both tests in =AND(B8>100,MIN(B2:B7)>=15) hold, so the cell answers TRUE. The second screen asks whether any item is under 20 or the total is over 200, and Bolt's 18 settles it on the first test alone.
Most mistakes here come from choosing between AND and OR. On data like this either function answers TRUE, so a wrong choice stays hidden until a month when the two tests disagree.
Syntax
=AND(test1, test2, ...) needs every test; =OR(...) needs one; =NOT(test) flips itWorked example
On Stock list with a total row and a smallest item, =AND(B8>100,MIN(B2:B7)>=15) returns true.
=AND(B8>100,MIN(B2:B7)>=15)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | An argument is text where a logical test belongs, as when a word reaches AND without quotes. |
#NAME? | A function name is mistyped, so no test runs at all. |
One test only | A formula holding a single comparison looks complete but skips the second condition, so a short item still passes. |
At work
Marking a month for restock only when the stock total is low and one line has run short.