Nurse staffing arithmetic on a 40-bed railway hospital ward, and a broken spreadsheet
A 40-bed general medical ward in a zonal railway hospital is sanctioned 24 staff nurses. The head nurse and two nursing attendants work additional hours and are not counted in the nurse–patient ratio. The divisional norm is one staff nurse for every six in-patients in each of the three shifts. Staff nurses work 6 days a week with one weekly off, and hospital planning assumes a further 10% reserve for leave, training and sickness. Last month the ward recorded 1,080 patient-days over 30 days, and on several nights occupancy reached the full 40 beds. The duty roster is kept in a spreadsheet with columns: A Date, B Name, C Shift (D/E/N/OFF), D Designation, E Hours. In cell F2 the formula =SUM(E2:E31) is displayed as #VALUE! and the head nurse asks the staff nurse to explain and fix it before the monthly return is submitted to the nursing superintendent.
1. Calculate the number of staff nurses required per 24 hours and the sanctioned requirement including weekly-off relief and the 10% reserve. Compare with the sanctioned strength and state what the shortfall means.
Read the worked answer
Per shift at full occupancy: 40 ÷ 6 = 6.67, which must be rounded up to 7 nurses. A fractional nurse cannot be posted, and rounding down leaves six patients without a named nurse.
Per 24 hours: 7 × 3 shifts = 21 nurse-shifts.
Weekly-off relief: each nurse works 6 days in 7, so multiply by 7/6 = 1.167. 21 × 1.167 = 24.5 → 25 posts.
Leave/training/sickness reserve of 10%: 25 × 1.10 = 27.5 → 28 posts.
Comparison: sanctioned 24 against a requirement of 28 → a shortfall of 4 posts, about 14%.
What this means for occupancy: last month's average occupancy was 1,080 ÷ 30 = 36 patients. At 36, the per-shift need is 36 ÷ 6 = 6 nurses, 18 per day, and 18 × 1.167 × 1.10 = 23.1 → 24 - just adequate. So the ward is funded for average occupancy and becomes unsafe whenever it fills to 40 beds, which happened on several nights.
Action: present the arithmetic, not an impression, to the head nurse and nursing superintendent, with the night occupancy figures attached. A staffing calculation with the steps shown is the strongest evidence for redeployment or for limiting admissions to a safe bed strength.
2. With four nurses fewer than the calculated requirement, how would you prioritise care, what may safely be delegated to nursing attendants, and what would you escalate and record?
Read the worked answer
Prioritise by acuity, not by equal bed allocation. Allocate the sickest patients first and give the most dependent patients to the most experienced nurse; group observations into rounds; use a short safety huddle at every shift change to flag the deteriorating patients, the falls risks and the patients with intravenous lines; site the most dependent patients near the nurses' station and cohort the rest.
May be delegated to nursing attendants: bed-making support and patient positioning within their training, transport to radiology, specimen delivery, linen and stock replenishment, serving meals and assisting stable patients to eat or wash, and running messages. These are support activities, not restricted nursing acts.
Must never be delegated to an unregistered worker: drug administration, assessment and triage, monitoring of a deteriorating or sedated patient, intravenous cannulation and infusion management, wound and stoma care, catheterisation, documentation of care, and the decision to escalate or not.
Escalate and record: inform the head nurse and nursing superintendent in writing, request redeployment or a partial closure of beds, inform the duty medical officer of the risk, record the shortfall in the shift report, and file an incident report for any adverse event or near miss. Deferring elective admissions is a management decision - it must be escalated, not made silently by the ward nurse at 22:00.
3. Explain why =SUM(E2:E31) shows #VALUE! and write correct formulas: one to count night duties in C2:C31 and one to total the hours worked by a named nurse.
Read the worked answer
Why #VALUE!: SUM ignores genuinely empty cells but cannot add text, and the error means at least one cell in E2:E31 contains text rather than a number. Typical culprits in a hand-maintained roster are hours typed as "8 hrs", as a time range "08:00–14:00", as "OFF" or "-", or a space typed into an apparently blank cell. A single text entry anywhere in the range returns #VALUE! for the whole formula.
Fix the data first: keep column E purely numeric (8, 0, 6) and put shift codes and remarks in their own columns. Then either use =SUM(E2:E31), or if text must remain, use =AGGREGATE(9,6,E2:E31), which sums while ignoring errors and text, or =SUMPRODUCT(--ISNUMBER(E2:E31),E2:E31).
Count night duties: =COUNTIF(C2:C31,"N") - the shift code must match exactly, so check for stray spaces and use "N*" with wildcards only if codes such as N1, N2 are used.
Total hours for one named nurse: =SUMIF(B2:B31,"Name",E2:E31), or =SUMIFS(E2:E31,B2:B31,"Name",C2:C31,"N") for that nurse's night hours only. A pivot table is faster for the whole roster.
Before submitting: check the column letters against the actual sheet, verify the total against a manual count for one nurse, and save a copy - a duty roster is a legal record of who was on duty.