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 — for Human Resources
Divide leavers by average headcount to get a turnover percentage.
Count how many people sit in a single department on a staff list.
COUNTIF
Stamp the current date into a cell as a fixed value.
Subtract leave taken from entitlement to see what each person has left.
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
Return the right rate from several tiers without nesting.
IFS
Count people in one department whose last review was long enough ago.
COUNTIFS
Check whether a salary falls below or above its grade band.
OR · IF
Add up overtime for a single team out of a mixed timesheet.
SUMIF
Diagnose and correct a formula that returns the wrong answer.
IF
| # | Exercise | Functions | Difficulty | Time | Status |
|---|---|---|---|---|---|
| 6 | Calculate the annual turnover rateHarbor Supply — annual turnover summary | — | Beginner | 4 min | 10 pts |
| 9 | Count the headcount in one departmentHarbor Supply — staff list by department | COUNTIF | Beginner | 4 min | 10 pts |
| 14 | Type today's date without typing itNo dataset — this one is about your hands | — | Beginner | 3 min | 10 pts |
| 18 | Work out remaining leave daysHarbor Supply — annual leave tracker | — | Beginner | 3 min | 10 pts |
| 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 |
| 50 | Apply commission tiers with IFSHarbor Supply — Q3 sales by rep | IFS | Intermediate | 10 min | 25 pts |
| 54 | Count staff overdue a reviewHarbor Supply — months since last review | COUNTIFS | Intermediate | 8 min | 25 pts |
| 56 | Flag salaries outside their bandHarbor Supply — pay band audit | OR · IF | Intermediate | 8 min | 25 pts |
| 61 | Total overtime hours for one teamHarbor Supply — overtime by team | SUMIF | Intermediate | 7 min | 25 pts |
| 72 | Fix a broken commission formulaHarbor Supply — commission run with a bug | IF | Advanced | 14 min | 40 pts |