IntermediateLogical Functions
Band orders by size with nested IF
Return one of three labels depending on where a value falls.
IF
9 min25 points
Work out fill rate without a divide error
The situation
Harbor Supply reports fill rate as units shipped over units ordered. Cancelled lines show zero ordered, and dividing by zero fills the column with #DIV/0! — which then breaks every total further down the sheet.
A1
fx
| Row number | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Line | Ordered | Shipped | Fill rate | |
| 2 | L-101 | 200.00 | 180.00 | ||
| 3 | L-102 | 0.00 | 0.00 | ||
| 4 | L-103 | 150.00 | 150.00 | ||
| 5 | L-104 | 0.00 | 0.00 | ||
| 6 | L-105 | 90.00 | 72.00 | ||
| 7 |
Functions in this exercise
- IF — Returns one value when a test is true and another when it is false.
Who this is for
Recommended next
Related exercises
- AdvancedLookup & Reference
Handle missing lookups gracefully
Return a readable message instead of #N/A when a lookup finds nothing.
XLOOKUP
10 min40 points - BeginnerLogical Functions
Flag reps below target with IF
Use IF to label each rep as meeting or missing their target.
IF
5 min15 points - BeginnerLogical Functions
Flag orders needing special handling with OR
Mark an order when either of two independent conditions is true.
OR · IF
5 min15 points