# How to Determine The First Case that Meets Multiple Criteria

#### Wolfster63

##### New Member
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

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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### JamesCanale

##### Active Member
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")

#### johnnyL

##### Well-known Member
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
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
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
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!

Replies
2
Views
101
Replies
3
Views
131
Replies
3
Views
103
Replies
9
Views
123
Replies
5
Views
126

1,148,425
Messages
5,746,603
Members
424,032
Latest member
pochie2741

### 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.

### Which adblocker are you using?

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

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