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.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 teachesMay contain 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.