Ward occupancy chart

[PeterExcel]

Hi all,

I have a list of patients in a ward, with admission times and discharge
times, as date-time values.

What I want is a line chart of ward occupancy - how many patients are
in the ward at any one time.

What I've tried so far is writing a list of each hour over the study
period (three months) and putting a COUNTIF statement adjacent to each
hour, referring to that hour.

But the COUNTIF statement can't take a reference as the criteria.

Any ideas gratefully accepted.

Using Excel 97 SR2, Win NT 4.0.

TIA, Peter

 

[Don Guillett]

try something like
=sumproduct((a2:a200=a1)*(b2:b200))
where col a has the time and col B has the names

 

[Max]

One approach:

Assume you have in Sheet1, the date-time data for
Admissions & Discharges (1 row per patient)
in cols A & B (data from row2 down)

For example:

Admissions........Discharges
12/30/03 1:15 12/31/03 4:45
12/31/03 1:45 12/31/03 5:52
12/31/03 2:15 12/31/03 4:03
12/31/03 3:15 12/31/03 4:10
12/31/03 3:30 12/31/03 5:35
12/31/03 3:30 12/31/03 7:05
12/31/03 5:30 12/31/03 6:15
12/31/03 7:30 12/31/03 8:56
12/31/03 8:04 12/31/03 8:30
12/31/03 8:15 12/31/03 10:30

In Sheet2, in cols A - F is your Summary Table, viz.:
----------------------------------------------------------
Date - Time....Admission...Discharge...Cumu Ad..Cumu Disch..Occupancy
12/31/03 1:00
12/31/03 2:00
12/31/03 3:00
12/31/03 4:00
12/31/03 5:00
12/31/03 6:00
12/31/03 7:00
12/31/03 8:00
12/31/03 9:00
etc

Date - Time (Col A)
---------------------
This col is where you list the date-time by hourly bands

Admission (Col B)
----------------
Put in B2
: =COUNTIF(Sheet1!A:A,">"&$A2)-COUNTIF(Sheet1!A:A,">="&$A3)
Copy B2 down the col

Discharge (Col C)
------------------
Put in C2
: =COUNTIF(Sheet1!B:B,">"&$A2)-COUNTIF(Sheet1!B:B,">="&$A3)
Copy C2 down the col

Cumulative Admissions / Discharges (Cols D & E)
-----------------------------------------------------
Put:

in D2: =B2

in D3: =D2+B3
Copy D3 down the col

Select D2xx, copy across to Exx
(where xx = last row in col D)

Occupancy (Col F)
----------
Put in F2: =D2-E2
Copy F2 down col F

[Occupancy* = Cumulative Admissions - Cumulative Discharges]
*per hourly band

Now you can use cols A & F to plot a "horizontal' clustered bar-chart
which shows occupancy against date-time (by hourly bands)

 

[PeterExcel]

Max,

Your formula worked a charm.

The trick was knowing how to reference with the
COUNTIF statement.

Thanks, Pete

 

[Max]

Thanks for feedback, Peter !
Glad it worked for you.

cheers
Max
xl 97