Pay a shift at the right rate, with the night premium on top
One formula, two lookups: what this person earns an hour, and whether tonight costs more.
Task:
You run payroll for a bakery that trades through the night. Five staff, each with a role and a shift, and a rate card in G1:H4 that pays by role rather than by person — night shifts earn the premium in H6 on top. Work out each person's pay in E2:E6, then total the payroll in E7. Write E2 so that copying it down the column just works.
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 | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Role | Hours | Shift | Pay | Role | Rate | |
| 2 | Maya Okonkwo | Barista | 32 | Day | Barista | 12.5 | ||
| 3 | Tomas Reiter | Baker | 38 | Night | Baker | 15 | ||
| 4 | Priya Nandi | Supervisor | 40 | Night | Supervisor | 18.75 | ||
| 5 | Lars Ohlin | Barista | 28 | Night | ||||
| 6 | Ines Duarte | Baker | 35 | Day | Night premium | 0.15 | ||
| 7 | Total payroll |
What this exercise teachesMay contain the answer
The rate card is the point of the exercise: pay is filed by role, not by person, so a rate typed into each row would have to be retyped every time the card changes. VLOOKUP goes and fetches it instead, and FALSE as the fourth argument insists on an exact match on the role — leave it off and VLOOKUP assumes the card is sorted and quietly returns the nearest thing below, which on a rate card means paying somebody the wrong wage without an error to show for it. The premium is folded in as (1+IF(D2="Night",$H$6,0)) rather than added afterwards, so the multiplier is 1.15 on a night and exactly 1 on a day, and one formula covers both cases without a second column. Everything the row owns stays relative and everything shared is absolute, which is what makes the single formula in E2 survive being copied down — and what makes changing the premium in H6 repay the whole column at once.