Overtime pay without an IF
Federal overtime rules in two functions: MIN and MAX, not a nested IF.
Task:
You do payroll for a small warehouse crew. Weekly overtime is anything over 40 hours, paid at 1.5 times the hourly rate. For each employee, work out the regular pay in column D and the overtime pay in column E.
Learning Objectives:
- Cap a value with MIN instead of a conditional
- Floor a value at zero with MAX instead of a conditional
- Split one number into two pieces that always sum to the total
Solve without hints for +5 XP
Interactive Spreadsheet
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 | Employee | Hours Worked | Hourly Rate | Regular Pay | Overtime Pay |
| 2 | Rosa Delgado | 45 | 18 | ||
| 3 | Kwame Boateng | 38 | 22 | ||
| 4 | Lina Petrov | 52 | 20 |
What this exercise teaches (contains the answer)
MIN(hours,40) and MAX(hours-40,0) split one number into two pieces that always add back up to the total, with no branch to get wrong: below 40 hours, MIN returns the actual hours and MAX returns zero; above it, MIN caps at 40 and MAX carries the rest. A threshold IF would need two branches each repeating the same arithmetic; these two functions are the branches, without the repetition.