Real situations from working spreadsheets — sales targets, commission tiers, messy exports, aged invoices. Numbered in recommended order: start at 1 and work down, or filter to what you need.
11 exercises — Logical Functions
Use IF to label each rep as meeting or missing their target.
IF
Mark an order when either of two independent conditions is true.
OR · IF
Require two conditions to be true at once.
IF · AND
Return one of three labels depending on where a value falls.
IF
Mark a campaign only when it overspends by more than a tolerance.
IF
Guard a division so cancelled lines do not fill the report with errors.
IF
Return the right rate from several tiers without nesting.
IFS
Combine two logical tests so one condition can override the other.
AND · OR · IF
Check whether a salary falls below or above its grade band.
OR · IF
Read a code's prefix and translate it into a readable category name.
IFS · LEFT
Diagnose and correct a formula that returns the wrong answer.
IF
| # | Exercise | Functions | Difficulty | Time | Status |
|---|---|---|---|---|---|
| 30 | Flag reps below target with IFHarbor Supply — Q3 rep performance with achievement | IF | Beginner | 5 min | 15 pts |
| 32 | Flag orders needing special handling with ORHarbor Supply — warehouse pick list | OR · IF | Beginner | 5 min | 15 pts |
| 43 | Approve orders with ANDHarbor Supply — order approval queue | IF · AND | Intermediate | 8 min | 25 pts |
| 45 | Band orders by size with nested IFHarbor Supply — October orders | IF | Intermediate | 9 min | 25 pts |
| 47 | Flag campaigns over budget by a marginHarbor Supply — campaign budget versus actual | IF | Intermediate | 8 min | 25 pts |
| 49 | Work out fill rate without a divide errorHarbor Supply — fill rate by line, some cancelled | IF | Intermediate | 8 min | 25 pts |
| 50 | Apply commission tiers with IFSHarbor Supply — Q3 sales by rep | IFS | Intermediate | 10 min | 25 pts |
| 55 | Flag accounts for review with AND and ORHarbor Supply — accounts receivable review | AND · OR · IF | Intermediate | 9 min | 30 pts |
| 56 | Flag salaries outside their bandHarbor Supply — pay band audit | OR · IF | Intermediate | 8 min | 25 pts |
| 64 | Turn a product code into a categoryHarbor Supply — SKU category decode | IFS · LEFT | Intermediate | 9 min | 30 pts |
| 72 | Fix a broken commission formulaHarbor Supply — commission run with a bug | IF | Advanced | 14 min | 40 pts |