How to Determine The First Case that Meets Multiple Criteria

Wolfster63

New Member
Joined
May 2, 2018
Messages
23
I work in a busy surgical unit. We have a performance metric called a "First Case On Time Start" or FCOTS.

I have been able to determine our performance to date using a Pivot table and helper cells on a very large sorted spread sheet that contains the surgery date, the operating room name, the start time, and the surgeon. It would be much more efficient if I could determine which cases meet the FCOTS criteria and display a simple "Y" or "N".

The Criteria are as follows:
  1. FCOTS are classified as surgical cases that start between 6:30 AM and 9:00 AM for a surgeon on a given day.
  2. Only the first surgeon in the time period in the room qualifies to be an FCOTS case. For example, if Doctor Jones has a case that started at 7:00 AM in OR01 and Doctor Smith has a case in OR01 on the same day that starts at 8:30 AM, Only Doctor Jones' case qualifies.
  3. Only the surgeon's first case of the day qualifies. For example, if Doctor Jones has a case at 7:00 AM in OR01 and then another case in OR01 at 8:45 AM, only the first case qualifies.
  4. If the surgeon has cases in two in two different rooms, only the first case qualifies.

I am running Excel 2016 on Windows 10 Enterprise, Ver. 1909, OS Build 18363.1679.

Because of security protocols at my institution, I can't upload a mini sheet. Here is a pasted version of the test spreadsheet I created. I also attached a jpeg of the same.

Any ideas of formulas would be welcome.

Will

FCOTS.JPG


The table with sample data:

DateRoomStart TimeSurgeonFCOTS? (Y or N)
8/1/2021OR016:29Smith
8/2/2021OR027:00Jones
8/2/2021OR028:50Smith
8/3/2021OR034:50Garcia
8/4/2021OR047:01Dewey
8/1/2021OR017:45Smith
8/3/2021OR036:30Garcia
8/3/2021OR038:45Jones
8/2/2021OR018:59Garcia
8/3/2021OR048:14Smith
8/3/2021OR0310:00Jones
8/3/2021OR0311:35Smith
8/4/2021OR048:50Cheatam
8/1/2021OR019:00Jones
8/5/2021OR019:30Dewey
8/5/2021OR038:03Howe
8/4/2021OR0414:00Howe
8/4/2021OR016:00Jones
8/2/2021OR027:00Garcia
8/2/2021OR0320:00Jones
8/5/2021OR047:00Cheatam
8/3/2021OR027:25Dewey
8/3/2021OR029:00Howe
8/5/2021OR0411:00Cheatam
8/4/2021OR029:02Smith
8/4/2021OR048:03Garcia
8/5/2021OR039:06Howe
8/1/2021OR0112:00Jones
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
390
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Take a close look at this - I'm not sure if I got the logic right... I set this to look to see if a particular row was the first operation of the day (day from 6:30 to 9) for the operating room AND the first operation of the day for the doctor. I did it in Office2016 and 365 (for my own amusement).

Mr Excel Playground 3.xlsm
ABCDEF
1DateRoomStart TimeSurgeonFCOTS (old excel)FCOTS? (Y or N) EXCEL365
28/1/2021OR016:29SmithNN
38/2/2021OR027:00JonesYY
48/2/2021OR028:50SmithNN
58/3/2021OR034:50GarciaNN
68/4/2021OR047:01DeweyYY
78/1/2021OR017:45SmithYY
88/3/2021OR036:30GarciaYY
98/3/2021OR038:45JonesNN
108/2/2021OR018:59GarciaNN
118/3/2021OR048:14SmithYY
128/3/2021OR0310:00JonesNN
138/3/2021OR0311:35SmithNN
148/4/2021OR048:50CheatamNN
158/1/2021OR019:00JonesNN
168/5/2021OR019:30DeweyNN
178/5/2021OR038:03HoweYY
188/4/2021OR0414:00HoweNN
198/4/2021OR016:00JonesNN
208/2/2021OR027:00GarciaYY
218/2/2021OR0320:00JonesNN
228/5/2021OR047:00CheatamYY
238/3/2021OR027:25DeweyYY
248/3/2021OR029:00HoweNN
258/5/2021OR0411:00CheatamNN
268/4/2021OR029:02SmithNN
278/4/2021OR048:03GarciaNN
288/5/2021OR039:06HoweNN
298/1/2021OR0112:00JonesNN
Sheet19
Cell Formulas
RangeFormula
E2:E29E2=IF(AND(MIN(IFERROR(1/(1/(($A$2:$A$29=A2)*($B$2:$B$29=B2)*($C$2:$C$29>=6.5/24)*($C$2:$C$29<=9/24)*$C$2:$C$29)),1))=C2,MIN(IFERROR(1/(1/(($A$2:$A$29=A2)*($D$2:$D$29=D2)*($C$2:$C$29>=6.5/24)*($C$2:$C$29<=9/24)*$C$2:$C$29)),1))=C2),"Y","N")
F2:F29F2=IF(AND(MINIFS($C$2:$C$29,$A$2:$A$29,A2,$C$2:$C$29,">="&6.5/24,$C$2:$C$29,"<="&9/24,$B$2:$B$29,B2)=C2,MINIFS($C$2:$C$29,$A$2:$A$29,A2,$D$2:$D$29,D2,$C$2:$C$29,">="&6.5/24,$C$2:$C$29,"<="&9/24)=C2),"Y","N")
 
Solution

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,846
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
How do two surgeons at the same time and same day get scored? ie. both at 7:00 8/2/2021 (Jones & Garcia)

Other than that, I also see two 'Y's for 8/5/2021 at different scheduled times. (Cheatam & Howe)
 

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
390
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Basically, a "Y" means that - after filtering out all start times that are outside the window - if it's the first case for the surgeon and the first case for the room, it's a "Y". So for the 8/5 case, since they are both in the time window, and they are different rooms and different surgeons, they both get a "Y". But does that mean I have the logic wrong?
 

Wolfster63

New Member
Joined
May 2, 2018
Messages
23
I used the formula in E2 and it appears to work in 18000+ lines of data going back 2 years.

Much Thanks.

The surgeon only has one first case counted on any given day. So only the first case counts, the second case does not. Even if the surgeon goes to a different room.

It is based on each surgeon individually. In essence, it's "his/her" first case of the day, and did he/she start on time. This kind of determination is used to see if a surgeon's reservation of times in an operating room are being honored. In a busy OR like ours, if a surgeon habitually can't start his/her first case of the day on time, it can severely inconvenience other surgeons scheduled later in the day. It is just one factor looked at.

Again, much appreciated.

Will
 
Last edited:

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,846
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Basically, a "Y" means that - after filtering out all start times that are outside the window - if it's the first case for the surgeon and the first case for the room, it's a "Y". So for the 8/5 case, since they are both in the time window, and they are different rooms and different surgeons, they both get a "Y". But does that mean I have the logic wrong?
The customer is happy with it, so I don't see a need to address it at this point.

Good job!
 

Forum statistics

Threads
1,148,011
Messages
5,744,334
Members
423,863
Latest member
teehexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top