SUMPRODUCT ON TIMES
 

Iain,

Good detail, you are hearing us :-).

Although you don't state where the problem lays, dates or times, I think the
way that you have tested times is incorrect. Instead of {"02:00:00"},
try --"02:00:00", e.g.

b10:
=SUMPRODUCT((DATA!A2:A2000=B42)*(DATA!A2:A2000<=C 42)*(DATA!F2:F2000<(--"02:
00:00")))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Iain Halder" wrote in message
...
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<={"0
2: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'<

Iain,

How do you determine whether they are blank? I ask this as I wonder if they
are really empty blank, or a space blank (IYSWIM).

I had this data

28/12/2004 01:59:59
28/12/2004 05:00:00
30/12/2004 01:59:59
28/12/2004

and with
28/12/2004 in both B42 and C42, the < 2 hour test gave me 2 (as
expected).

When I changed the test to 4 hours, I got 1, again as I should? If I
change that blank to a single space, I get 2.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Iain Halder" wrote in message
...
Hi,

Thank you for your reply!

I was thinking the error lay in the area of testing the times but even
applying your remedy makes only a partial correction.

If I say ..... <(--"02:00:00")))

I get the correct answer.

However when I try to apply instead I get a nonsense.

I printed out the DATA I was working with and discovered that when I
calculate for a figure 04:00:00 I get that figure PLUS the figure for
blanks!!! The two separate figures being added together.

I find the whole time calculation becomes correct when I subtract the
total for blanks from the figure for 4 hours.

This cannot be right.

Is there something I am missing here? How can Excel be adding blank
fields (which are surely 0) to fields for 4 hrs?

Iain.H

On Thu, 13 Jan 2005 13:05:02 -0000, "Bob Phillips"
wrote:

Iain,

Good detail, you are hearing us :-).

Although you don't state where the problem lays, dates or times, I think
the
way that you have tested times is incorrect. Instead of {"02:00:00"},
try --"02:00:00", e.g.

b10:

=SUMPRODUCT((DATA!A2:A2000=B42)*(DATA!A2:A2000<= C42)*(DATA!F2:F2000<(--"02
:
00:00")))

Rescued Cats & Kittens Needing Homes
'o'< www.celiahammond.org 'o'<
'o'< www.cat77.org.uk 'o'<