BeginnerLogical Functions
Flag reps below target with IF
Use IF to label each rep as meeting or missing their target.
IF
5 min15 points
Replace #DIV/0! with a dash using IFERROR
The situation
Harbor Supply's monthly returns report divides returns by orders for every region. Export shipped nothing this month, so its row shows #DIV/0! and the regional manager asked whether the report is broken. It is not — but a report should say 'no orders' in plain terms rather than show an error code.
A1
fx
| Row number | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Region | Orders | Returns | Return rate | |
| 2 | Export | 0.00 | 0.00 | ||
| 3 | East | 120.00 | 6.00 | ||
| 4 | West | 85.00 | 2.00 | ||
| 5 | North | 64.00 | 4.00 | ||
| 6 |
Functions in this exercise
- IFERROR — Returns a value you choose when a formula errors, and the formula's result otherwise. Syntax and example
Who this is for
Recommended next
Related exercises
- IntermediateLogical Functions
Work out fill rate without a divide error
Guard a division so cancelled lines do not fill the report with errors.
IF
8 min25 points - 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