Occupancy Rates

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

Thanks to everyone with the help from the last couple of days. Here is my
latest request (although this one is considerably more complex)


A B C D E
F G H I
J K
Date First Name Surname Sex From To
Consultant Speciality Date Discharged Destination Length
of Stay
12/12/2003 Tom Cat M 63 18
Doctor A Opthamology 13/12/2003 Home 1
12/12/2003 Freda Bloggs F 63 19
Doctor B Cardiology 14/12/2003 Home
2
13/12/2003 Free Willy F 63 18
Doctor A Cardiology 20/12/2003 Home 7


What I would like is another workbook linked (with days of months) to the
above workbook which adds up patient addmissions and discharges on a daily
basis for each individual ward (ie 18 , 19, 17 etc), also occupancy rates of
ward for any day so that in the above example on the 13/12 I had 2 pt's on
ward 18 (Tom Cat admitted 12/12 + Free Willy admitted on 13/12) but by the
14/12 the number is reduced to 1 pt. (Tom Cat has gone home). Any ideas how
I can formulate a formula so that the linked workbook gives numbers of
patients admitted and discharged and occupancy rates (ward 18 has 28 beds so
21 patients would give an occupancy rate of 75%).

Thank You for your assistance

Sandy
 
Sorry it hasn't formatted correctly, it should have had Column labels across
the screen thus A=Date B=First Name C=Surname D= Sex E=From
F=To G= Consultant H= Speciality I=Date Discharged J=
Destination K= Length
then with patient details below
 
Assuming that you have a sheet for each ward then lets also assume
that you have the total number of beds in cell A1. Formula (untested)
could be:

={(countif(F5:F55,">="&today())-countif(E5:E55,">"&today()))/a1}

where range F5:F55 is the 'Date To' field and E5:E55 is the 'Date
From' field.

Note the {} brackets round the formula as you will need to enter this
as an array formula (press ctrl shift and enter when editing it).

if you leave the /a1 off the end of the formula you will get the
patient numbers rather than the occupancy rate.

hth
 
Back
Top