Logical Functions
Intermediate
OR to flag any one of several problems
A row needs a look if anything at all is wrong with it.
Task:
Before importing orders you want to catch bad rows. In E2:E7 return TRUE when a row has no customer, a quantity of zero or less, or a price over 1,000 (almost certainly a typo).
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of AND, OR, NOT
0/3The same formula in the other shapes it takes at work.
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Customer | Qty | Price | Check? |
| 2 | SO-51 | Alder Cafe | 4 | 12.5 | |
| 3 | SO-52 | 2 | 8.75 | ||
| 4 | SO-53 | Birch & Co | 0 | 22 | |
| 5 | SO-54 | Cedar Deli | 6 | 1450 | |
| 6 | SO-55 | Dove Bakery | 3 | 18 | |
| 7 | SO-56 | Elm Bistro | -1 | 9.5 |
What this exercise teachesMay contain the answer
A validation column should answer one question — does this row need a human? — and OR is that question. Checking each problem in its own column works too, but then someone has to scan three columns; one TRUE/FALSE column is something you can filter on.