Chandoo published a useful tip recently for revealing the contents of hidden cells. Anyone who works in the NHS and ever has to deal with excel spreadsheets that originate for the DH will be very familiar with the frustration of working with workbooks that have been protected to the max.

I’ve been working on the Safer Nursing Tool recently. This is an interesting application originally produced by the Institute of innovation and Performance, for working out recommended staffing levels in hospital wards. I don’t think the tool is supported anymore so I’ve been recreating the methodology in a simpler workbook that we can control.

The original tool is very well protected and most cells are not even clickable. Using Chandoo’s technique I’ve been able to navigate my way around the workbook to reveal the formulas used for certain calculations.

I was trying to determine the formula used in the Rate of Slips, trips and falls per 1,000 beddays.

As you can see, the row and column headers have been removed so it required a bit of trial and error to find the cell I was interested in (remember that the sheet prevented me from directly clicking on the cell of interest).

By typing a cell address in the name box and refining until I found the right location, I was able to reveal the formula:

=IF(E15=””,””,IF(ISERROR(ROUND(SUM(E15/$I$16)*1000,3)),”Data Missing”,( ROUND(SUM(E15/$I$16)*1000,3))))

I also needed to identify $I$16 which turned out to be Average occupied Beds to determine that the original formula was reporting the rate of falls as (No of Falls reported in period) / (Avg Occupied Beds per day) * 1000

I’m pretty sure that is incorrect and it should be (No of falls reported in the period) / (Total Occupied Bed Days in Same Period) * 1000 which turns out to be a much more palatable figure.

The following website has some useful information on calculating incidences per 1000 beddays

Please note, all data is made up and Hope Ward is fictitious location based in a non-existent hospital.