Calculating numberr of patients seen every two hours in ED

[julesmctavish]

Hi;
I need to find out how many patients are seen every two hours per day of week for an entire year in the emergency department.
I've included a small data set that I have cleaned up from the electronic medical records.
What I am trying to discover:
The busiest times in the emergency department so that I can staff accordingly.

The actual data set is HUGE. It is for an entire year. But once I have a formula, I can calculate for the entire year.

And as an FYI: I have learned formulas on this forum that I have used so much in my work...so THANK YOU FOR YOUR HELP!!!!

[leelnich]

Hi Jules - You weren't specific about the counting process, so I did two columns. They're based on 30-minute intervals, as that's pretty standard for this type of problem. Fairly easy to lengthen them to 2 hours if you prefer.

Number of patients ADMITTED during each interval (in B2 and down):
=SUMPRODUCT((Sheet1!$C$2:$C$22 > = Sheet2!$A2)*(Sheet1!$C$2:$C$22 < Sheet2!$A2+1/48))

Number of patients ON-SITE during each interval (ARRAY FORMULA in C2 and down):

Please Login or Register  to view this content.

NOTE: Since Excel tracks time as fraction of a day, the 1/48 in each formula equals 30 minutes.

*You must press CTRL+SHIFT+ENTER to confirm entry of an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.



Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[julesmctavish]

THANK YOU!!!!!!!!!!!!!!!!!!
I never thought about using the sum product...I have been trying to figure it out with Pivot Tables!!!!
I so appreciate your time.

[leelnich]

Jules- Noticed a problem with second formula. It wasn't handling [patients admitted before midnight, discharged after] correctly. Here's the fix:
Number of patients ON-SITE during each interval (ARRAY FORMULA in C2 and down):

Please Login or Register  to view this content.

*You must press CTRL+SHIFT+ENTER to confirm entry of an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

NOTE: Also updated Post #2 formulae+workbook with above fix.

[julesmctavish]

that is great...
Now I can see how many are in the building (ED AND ACUTE CARE-)(when I figure that out)....that's awesome.
THANK YOU!!!!!!!!!!!!!!!
That's more than I thought could be done...Appreciate you taking the time this is so helpful....

[leelnich]

Happy to be of service, thank you for the rep! Take care-Lee

[leelnich]

Addendum- Workbook for multiple dates
Jules- As per PM request, a workbook that processes multiple dates. This actually simplified the ON-SITE calculation. I've used Named Ranges in the formulae, just change their Refers To: ranges as required. Here are the formulae (BOTH standard. NO MORE ARRAY FORMULA.)

Patients Admitted In A3:AX3 and down:
=SUMPRODUCT((ADMIT_DATE=$A3)*(ADMIT_TIME>=C$2)*(ADMIT_TIME<(C$2+Interval_Length)))

Patients On-Site In AY3:CT3 and down:
=COUNTIFS(ADMIT_DATE_TIME,"<" & ($A3+AY$2+Interval_Length),DISCH_DATE_TIME,">="&($A3+AY$2))

The Work area can be filtered by Date, Weekday, or both.

[julesmctavish]

OMG....this is amazing.
I have no words.
I am going to study this formula because that is how I learn.
But you have done is way beyond what I thought was possible.
THANK YOU so much. I am extremely grateful for your time and talent.
Now, I can get an idea of where to staff appropriately and use the formula to evaluate the acute care unit next.
MUCH gratitude............

[leelnich]

Happy to oblige! Sorry I didn't respond sooner to your PM, yesterday was busy for me. Take care. - Lee

[julesmctavish]

Never mind...
OMG...I've been working on this stuff too long.
Figured it out.

[julesmctavish]

Hi Lee;
Okay, so I have gotten this far on the Acute Care counts and it works great, but I don't know what I am doing wrong when I use the formula for the onsite counts. It doesn't seem right to me.
What am I doing wrong?

So close to getting the information needed.
If you have time to look at this....thank you ever so much.
Jules

[leelnich]

Right idea, but you're doing daily totals, so you need 1 row per day = 366 rows, since 2016 was a leap year. However, we have a problem. The COUNTIFS function was introduced in Excel 2007, but the workbook you sent pre-dates that. I'll re-write that formula, but can you find out what version your clinic is using?

[julesmctavish]

Hey Lee;
Sorry, I was in a meeting. That is so weird. I wonder if when the system dumps the information, it reverts to something older than what I thought we used.
I thought it was 2013, but obviously not.
Hmmmmmmmmm....
checking with IT to verify.

[leelnich]

It may just be a conscious choice to make the info compatible with the widest variety of "downstream" systems. I can re-write using SUMPRODUCT, but I've heard full-column references such as those used in your Name definitions may be a little unpredictable.

[julesmctavish]

And I see where I made the mistake on dates.
I am in the process of doing 366 days for the year.
Should I change the Naming construction?