Is an equal share of the work actually fair?
Two hundred and ninety-two scripts, four markers, four different speeds. The fair split is not an equal number of scripts, and the optimizer lands all four on the same hour to the minute.
Words on this sheet
- Objective: The one number Optimization is trying to push as high or as low as it can go.
School Starter Optimization free
After you install, this is the model to open.
Share the Marking Fairly
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
- Peak load after solving
- 11h 25m all four markers land on 11.42 hours to the minute
- The equal split's peak
- 14h 24m Tom's load when every pile is just cut four ways
- Given back
- 3 hours off the busiest marker's week, same 292 scripts
- Everyone still fits
- 12h cap Ana's availability, the tightest, holds at 11.42
Four papers, 292 scripts, four markers who are not equally fast and not equally free. The minutes-a-script block at the top varies both ways: Ana is quickest on the Stats 101 paper at six minutes because she taught it, Sam is quickest on the essays. The sheet opens on the split every department defaults to, which is to cut each pile four ways: 23, 17.5, 16.5 and 16 scripts each.
That gives Priya 14 hours 6 minutes, Tom 14 hours 24, Ana 11 hours 40 and Sam 12 hours 36, and since nobody breaks their availability it looks fair. Click Run and all four land on 11.42 hours, which is 11 hours 25 minutes each, to the minute. The busiest marker loses three hours of their week and the total marking time falls from 52.8 hours to 45.7, because work moves to whoever is fast at that particular paper.
Ana ends up with the whole Stats 101 pile, which is the one part of the answer that holds however the rest is arranged, because six minutes against eight, nine and eleven is too big an edge to leave anywhere else. Look at what fair turned out to mean: not the same number of scripts, and not the same share of every pile, but the same number of hours.
An equal split of the work is only fair when everyone works at the same speed, and nobody does. Two things about the shape of this model, because they are the reusable part. The peak is a variable cell rather than a formula, each marker's hours minus that peak is held at or below zero, and the objective simply restates the peak, so minimizing it presses that bound down onto whoever ends up busiest.
Writing it any other way, in particular pointing a constraint at a formula cell on its right-hand side, reads that cell once before the run and then holds it fixed, which quietly turns the problem into a different one. And the answer comes back in fractions of a script: round to whole scripts and the peak moves by a minute or two, which is close enough for a rota.
Second run: cut Ana's availability to 8 hours, which is what one person having a bad week looks like, and rerun. The other three rise together to 12.73 hours, about an hour and twenty minutes more each. That is what one absence actually costs the rest of the team, and it is a better answer than asking people to absorb it. The model knows nothing about second marking or moderation and it assumes any marker may mark any paper.
To use it, retype the four papers and four names, time yourself on ten scripts to fill in the minutes block, and put real figures into the hours-available line.
The model
It arrives on a tab called Template: Sharing the Marking, carrying these columns:
- Sam (min/script)
- Scripts (count)
- Allocated (must equal scripts)
with the model computed beside the data:
| Hours each | 14.1 |
| Hours above the peak | -0.3 |
| Hours above availability | -0.9 |
| Objective: press that peak down | 14.4 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Did the course work or do the students just differ?Did the Course Move the Scores?
- Would more places actually fix the shortfall?Will the Programme Cover Its Costs?
- Is the cheapest fix on your risk list the best one?What Could Go Wrong This Term?
- Which class should go to the hired hall?Fit the Timetable Without a Clash
- Did the workshop actually move anyone?Did the Training Change Each Person's Score?
- How many teachers will next summer actually need?How Many Students Next Term?
Every model like this one, and the method behind them: Optimization in Google Sheets.