Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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'< |
#2
|
|||
|
|||
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'< |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
Adding/Averaging Times in Excel | Excel Discussion (Misc queries) | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |