Is the Roster Fair?
Put in the shifts each nurse works and get back who carries the weekends and nights. Eight nurses, twelve shifts each, and it still is not equal: one works five weekends and one works one. The month looks fair in total hours and is not fair in the hours anybody minds.
Nursing Starter Statistics free
After you install, this is the model to open.
Is the Roster Fair?
- 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.
What it does
A four-week roster for eight RNs. Every one of them works twelve shifts, so on the only measure anybody publishes, total shifts, the roster is perfectly equal. Look at the columns nobody totals. Weekend shifts run from five down to one, a gap of four across a team doing identical hours. Nights run from eight down to two. Alvarez works five weekends and eight nights; Hume works one weekend and two nights.
Same twelve shifts, an entirely different month. With Descriptive Statistics selected, click Run on the weekend column: the mean is 3.25 with a standard deviation of 1.67, which is half the mean again, and a spread that wide is not a rota, it is a pattern. That summary names nobody, because a summary of a column is a dozen numbers standing in for every row that went into it.
When you want the nurses themselves, run Rank and Percentile on the same column: it prints one line per nurse, in order, and carries the name across from the Nurse column. Then the requests. Twenty-two days off were asked for and fourteen were granted, so 64% overall, which sounds reasonable until you read it per person. Alvarez asked for three and got one.
Chen asked for four and got one. Gore asked for five and got two. Boyd, Dulac, Fisk and Hume got everything they asked for. The people carrying the most weekends and the most nights are also the people whose requests get refused, and that correlation is what turns a scheduling annoyance into a resignation. This template does not build a roster and does not try to.
It takes the one you have already posted and puts a number on the thing people argue about, which is a different and more useful job: a charge nurse defending a roster in a corridor does not need an optimizer, they need to know whether the complaint is true. Here it is true. Three things it cannot see. It does not know who asked for the weekends, because some people want them for the differential.
It does not know who swapped with whom after the roster went up, so the worked roster and the posted roster are not always the same document. And it counts shifts rather than workload, which is the largest of the three. A shift is one row here whatever happened on it, so nothing in these numbers knows about patient acuity, about admissions, transfers and discharges, about how many patients needed total care, about how much of the shift went on chasing physicians, or about who had the complex equipment running.
Two nurses with five weekends each can have had entirely different months, and the nurse who draws the heavy assignments every time will look perfectly fairly rostered on every one of these counts. What this template does is put a number on the part of fairness that is countable from a posted roster, which is a real part of it and not the whole of it.
If the argument on your unit is about load rather than dates, add your own headings for the load you can actually measure, put one number per nurse in them, and run the same summary on those. Total both. To make it yours, replace the eight names with your team and paste your own counts. It takes about ten minutes with a posted roster in front of you, and the answer is usually not the one either side expected.
The model
It arrives on a tab called Template: Is the Roster Fair, carrying these columns:
- Four-week roster, 8 RNs, medical unit
with the model computed beside the data:
| Most weekends worked (count) | 5 |
| Fewest weekends worked (count) | 1 |
| Weekend gap across the team (count) | 4 |
| Most nights worked (count) | 8 |
| Fewest nights worked (count) | 2 |
| Days off asked for | 22 |
| Days off granted | 14 |
| Share of requests granted | 0.6364 |
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
- Put in the shift hours, the rate and your tax and costs and get back what the extra shift really leaves you. A twelve-hour overtime shift at $42 an hour with a night differential grosses $822 and leaves you $431.96. That is $36 an hour for a shift that pays $63, and just over half of what it looked like.Is the Extra Shift Worth It?
- Thirty-six hours of vacancy costs $2,268 on your own staff and $3,520 through an agency. The agency only wins if your overtime multiplier reaches 2.33, and almost nobody pays that.Overtime or Agency?
- Two nurses on call cost $624 on a normal night. Across ten thousand simulated nights they average $808, and the worst reaches $3,396, which is the number the stipend line never shows.How Many Nurses on Call?
- Put in the posts, the turnover and the time to fill and get back the empty posts and cover cost to expect. A 42-post unit at 19% turnover carries about two empty posts all year and $253,000 of cover. Filling posts faster matters more than losing fewer nurses, and the tornado says so.How Many Nurses Do We Need to Hire?
- Put in the patients, the nurses rostered and how often people call off and get back the odds the shift runs short. Twenty-seven patients, six nurses, four and a half each. The sheet says the shift is fine. Ten thousand simulated nights say it runs below ratio on a third of them, and call-offs, not the census, are why.Will the Roster Hold Tonight?
- Version B is up 18%. Is that a real win or just noise?Did the A/B Test Actually Win?
Every model like this one, and the method behind them: Statistics in Google Sheets.