Hi,
I'm with the NHS and doing a worksheet which looks at various aspects
of admission and referrals to an A&E Department. I'm using SUMPRODUCT
as an Excel method for calculating.
However, I'm getting a strange aberration on some of the time-based
numbers. I've had good, no excellent, advice here in the past for my
little Excel problems and hope somebody can help here again too.
Part of what this worksheet is for is to calculate waiting times from
entering the main hospital to being seen and discharged. The
Government says we must complete everything within 4 hours. This
worksheet shows us how many patients we are processing and within what
time.
The calculation is laid out as follows ...
WAITING TIMES
Within 2 Hrs
b10=sumproduct((data!a2:a2000>=b42)*(data!a2:a2000<=c42)*(data!f2:f2000<{"02:00:00"}))
Between 2 to 4 Hrs
b11=sumproduct((data!a2:a2000>=b42)*(data!a2:a2000<=c42)*(data!f2:f2000<={"02:00:00"}))-b10
After 4 Hrs
b12=sumproduct((data!a2:a2000>=b42)*(data!a2:a2000<=c42)*(data!f2:f2000>{"04:00:00"}))
Not Recorded
b13=sumproduct((data!a2:a2000>=b42)*(data!a2:a2000<=c42)*(data!f2:f2000={""}))
data! = Raw Data Sheet
a2:a2000 = List of dates in the form "28-Dec-04"
f2:f2000 = Times in format 00:00:00
b42 & c42 = From & To Dates
There are 55 patients in this week.
b10 resolves to 55 (wrong should be 24)
b11 resolves to <BLANK!> (wrong should be 9)
b12 resolves to <BLANK!> (wrong should be 3)
b13 resolves to 19 (correct)
B10, B11 & B12 are not resolving correctly at all. I'm assuming
(though may be wrong) that the error is in the latter part around the
< > = and time area but have not been able to resolve my mistake.
Can anybody please tell me what I am doing wrong here?
Iain Halder
Rescued Cats & Kittens Needing Homes
>'o'< www.celiahammond.org >'o'<
>'o'< www.cat77.org.uk >'o'<
Bookmarks