SUMPRODUCT ON TIMES

[Iain Halder]

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

 

[Bob Phillips]

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")))


--

HTH

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

 

[Iain Halder]

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

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

 

[Bob Phillips]

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]

You were right!

They were not blank blanks, very strange.

I think this is a product of ACCESS which is the database where the
spreadsheet is exported from. The option for this field comes up every
time the ACCESS database is used and it may be leaving a trace even
when nothing is actually input into the original field.

Thank you Bob for pointing that strange one out to me!!!

Iain

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.

Rescued Cats & Kittens Needing Homes