Counting Consecutive Days based on Criteria

[zcavaricci]

Hello,

I need to count the number occurences where blood sugar was >180 2x in a Day for =>3 consecutive days by patient. I have one table with the following structure:

[TABLE="width: 326"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date/Time[/TD]
[TD]Patient ID[/TD]
[TD]Glucose Reading[/TD]
[/TR]
[TR]
[TD]1/28/2017 17:36[/TD]
[TD]A[/TD]
[TD]192[/TD]
[/TR]
[TR]
[TD]1/28/2017 20:12[/TD]
[TD]A[/TD]
[TD]311[/TD]
[/TR]
[TR]
[TD]1/29/2017 9:58[/TD]
[TD]A[/TD]
[TD]214[/TD]
[/TR]
[TR]
[TD]1/29/2017 10:58[/TD]
[TD]A[/TD]
[TD]181[/TD]
[/TR]
[TR]
[TD]1/30/2017 20:45[/TD]
[TD]A[/TD]
[TD]279[/TD]
[/TR]
[TR]
[TD]1/30/2017 21:45[/TD]
[TD]A[/TD]
[TD]181[/TD]
[/TR]
[TR]
[TD]1/31/2017 7:48[/TD]
[TD]A[/TD]
[TD]157[/TD]
[/TR]
[TR]
[TD]1/31/2017 12:33[/TD]
[TD]A[/TD]
[TD]157[/TD]
[/TR]
[TR]
[TD]1/31/2017 17:48[/TD]
[TD]A[/TD]
[TD]167[/TD]
[/TR]
[TR]
[TD]1/28/2017 12:35[/TD]
[TD]B[/TD]
[TD]158[/TD]
[/TR]
[TR]
[TD]1/29/2017 12:09[/TD]
[TD]B[/TD]
[TD]190[/TD]
[/TR]
[TR]
[TD]1/29/2017 17:05[/TD]
[TD]B[/TD]
[TD]211[/TD]
[/TR]
[TR]
[TD]1/30/2017 7:41[/TD]
[TD]B[/TD]
[TD]197[/TD]
[/TR]
[TR]
[TD]1/30/2017 12:05[/TD]
[TD]B[/TD]
[TD]211[/TD]
[/TR]
[TR]
[TD]1/30/2017 17:31[/TD]
[TD]B[/TD]
[TD]231[/TD]
[/TR]
[TR]
[TD]1/31/2017 21:26[/TD]
[TD]B[/TD]
[TD]188[/TD]
[/TR]
[TR]
[TD]1/5/2017 22:05[/TD]
[TD]C[/TD]
[TD]205[/TD]
[/TR]
[TR]
[TD]1/5/2017 23:40[/TD]
[TD]C[/TD]
[TD]216[/TD]
[/TR]
[TR]
[TD]1/6/2017 3:18[/TD]
[TD]C[/TD]
[TD]195[/TD]
[/TR]
[TR]
[TD]1/6/2017 7:54[/TD]
[TD]C[/TD]
[TD]187[/TD]
[/TR]
[TR]
[TD]1/7/2017 12:54[/TD]
[TD]C[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD]1/7/2017 17:22[/TD]
[TD]C[/TD]
[TD]379[/TD]
[/TR]
[TR]
[TD]1/9/2017 7:37[/TD]
[TD]C[/TD]
[TD]191
[/TD]
[/TR]
</tbody>[/TABLE]


Results Expected:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Patient[/TD]
[TD]# Occurrences[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

I think I understood what you're after. Copy D2 down as necessary and do likewise with C29. The D column is a helper column and can be hidden if you so desire. I suppose one could incorporate column D into the C29 formula but it would sure look ungainly.

Keep in mind that the formula finds the number of occurrences that a patient had his glucose number more than 180 at least twice a day for at least 3 consecutive days.

	A	B	C	D
A
A
A
A
A
A
A
A
A
C
C
C
C
C
C
C
A
B
C

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Date/Time[/TD]
[TD="bgcolor: #FFF2CC"]Patient ID[/TD]
[TD="bgcolor: #FFF2CC"]Glucose Reading[/TD]
[TD="bgcolor: #FFF2CC"]>180 2x in a day[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/28/2017 17:36[/TD]

[TD="align: right"]192[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/28/2017 20:12[/TD]

[TD="align: right"]311[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/29/2017 9:58[/TD]

[TD="align: right"]214[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/29/2017 10:58[/TD]

[TD="align: right"]181[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/30/2017 20:45[/TD]

[TD="align: right"]279[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/30/2017 21:45[/TD]

[TD="align: right"]181[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1/31/2017 7:48[/TD]

[TD="align: right"]157[/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1/31/2017 12:33[/TD]

[TD="align: right"]157[/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1/31/2017 17:48[/TD]

[TD="align: right"]167[/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #D0CECE, align: right"]1/28/2017 12:35[/TD]
[TD="bgcolor: #D0CECE"]B[/TD]
[TD="bgcolor: #D0CECE, align: right"]158[/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #D0CECE, align: right"]1/29/2017 12:09[/TD]
[TD="bgcolor: #D0CECE"]B[/TD]
[TD="bgcolor: #D0CECE, align: right"]190[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #D0CECE, align: right"]1/29/2017 17:05[/TD]
[TD="bgcolor: #D0CECE"]B[/TD]
[TD="bgcolor: #D0CECE, align: right"]211[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #D0CECE, align: right"]1/30/2017 7:41[/TD]
[TD="bgcolor: #D0CECE"]B[/TD]
[TD="bgcolor: #D0CECE, align: right"]197[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #D0CECE, align: right"]1/30/2017 12:05[/TD]
[TD="bgcolor: #D0CECE"]B[/TD]
[TD="bgcolor: #D0CECE, align: right"]211[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #D0CECE, align: right"]1/30/2017 17:31[/TD]
[TD="bgcolor: #D0CECE"]B[/TD]
[TD="bgcolor: #D0CECE, align: right"]231[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #D0CECE, align: right"]1/31/2017 21:26[/TD]
[TD="bgcolor: #D0CECE"]B[/TD]
[TD="bgcolor: #D0CECE, align: right"]188[/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]1/5/2017 22:05[/TD]

[TD="align: right"]205[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]1/5/2017 23:40[/TD]

[TD="align: right"]216[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]1/6/2017 3:18[/TD]

[TD="align: right"]195[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]1/6/2017 7:54[/TD]

[TD="align: right"]187[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]1/7/2017 12:54[/TD]

[TD="align: right"]210[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]1/7/2017 17:22[/TD]

[TD="align: right"]379[/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]1/9/2017 7:37[/TD]

[TD="align: right"]191[/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FFFF00"]Results Expected:[/TD]
[TD="bgcolor: #FFFF00"]>180 2x in a Day for =>3 consecutive days[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FFE699"]Patient[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFE699"]# Occurrences[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"]1[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet14​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=SUM((B2=$B$2:$B$24)*(INT(A2)=INT($A$2:$A$24))*($C$2:$C$24>180))>=2}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C29[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(($D$2:$D$24=TRUE)*($B$2:$B$24=A29),ROW($D$2:$D$24)),IF(($D$2:$D$24<>TRUE)*($B$2:$B$24=A29),ROW($D$2:$D$24)))>=6,1,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]

<strike></strike>

 

[zcavaricci]

Thank DRSteele! I was hoping to have this done in MSACCESS? I know this is the MREXCEL site but there was a forum for MSACCESS so I thought I would put my question there. I will test with my data in excel until I can figure out in MSACCESS.

Michael

 

[DRSteele]

Sorry, my mistake. This keeps happening when I look through the Zero-reply posts - I read it and it's Excel and then it turns into Access later. Hmmmm,.

 

[zcavaricci]

No Problem, I can work with what you gave me, I still get my results, just need to import the data back to MSACCESS Table

 

[zcavaricci]

Seems to be a snafu: The formula doesn't take into account more that 6 days of >180 is still the same occurrence. I get 3 occurrences when there should be 1.

[TABLE="width: 452"]
<tbody>[TR]
[TD]Date and Time[/TD]
[TD]Patient ID[/TD]
[TD]Glucose[/TD]
[TD]>180 2x in a day[/TD]
[/TR]
[TR]
[TD]1/5/2017 3:35[/TD]
[TD]A[/TD]
[TD]189[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/6/2017 20:46[/TD]
[TD]A[/TD]
[TD]182[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/7/2017 4:16[/TD]
[TD]A[/TD]
[TD]181[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/9/2017 8:16[/TD]
[TD]A[/TD]
[TD]197[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/12/2017 7:44[/TD]
[TD]A[/TD]
[TD]191[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/12/2017 16:17[/TD]
[TD]A[/TD]
[TD]181[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/14/2017 4:33[/TD]
[TD]A[/TD]
[TD]185[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/15/2017 8:05[/TD]
[TD]A[/TD]
[TD]183[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/15/2017 20:44[/TD]
[TD]A[/TD]
[TD]181[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/16/2017 4:14[/TD]
[TD]A[/TD]
[TD]189[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/16/2017 15:27[/TD]
[TD]A[/TD]
[TD]182[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/17/2017 11:39[/TD]
[TD]A[/TD]
[TD]181[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/18/2017 16:00[/TD]
[TD]A[/TD]
[TD]185[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/19/2017 11:24[/TD]
[TD]A[/TD]
[TD]183[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1/24/2017 6:23[/TD]
[TD]A[/TD]
[TD]205[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/24/2017 15:24[/TD]
[TD]A[/TD]
[TD]199[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/24/2017 21:02[/TD]
[TD]A[/TD]
[TD]197[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/25/2017 5:42[/TD]
[TD]A[/TD]
[TD]218[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/25/2017 18:03[/TD]
[TD]A[/TD]
[TD]265[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/25/2017 21:39[/TD]
[TD]A[/TD]
[TD]208[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/26/2017 6:02[/TD]
[TD]A[/TD]
[TD]246[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/26/2017 10:11[/TD]
[TD]A[/TD]
[TD]215[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/26/2017 14:33[/TD]
[TD]A[/TD]
[TD]238[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/26/2017 21:20[/TD]
[TD]A[/TD]
[TD]192[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/27/2017 5:54[/TD]
[TD]A[/TD]
[TD]236[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/27/2017 9:51[/TD]
[TD]A[/TD]
[TD]215[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/27/2017 13:21[/TD]
[TD]A[/TD]
[TD]264[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/27/2017 21:46[/TD]
[TD]A[/TD]
[TD]255[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/28/2017 5:28[/TD]
[TD]A[/TD]
[TD]252[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/28/2017 10:09[/TD]
[TD]A[/TD]
[TD]226[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/28/2017 13:33[/TD]
[TD]A[/TD]
[TD]240[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/28/2017 21:57[/TD]
[TD]A[/TD]
[TD]230[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/29/2017 4:59[/TD]
[TD]A[/TD]
[TD]203[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/29/2017 13:00[/TD]
[TD]A[/TD]
[TD]224[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/29/2017 22:11[/TD]
[TD]A[/TD]
[TD]201[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]1/30/2017 6:21[/TD]
[TD]A[/TD]
[TD]201[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient[/TD]
[TD][/TD]
[TD]Occurrence[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[DRSteele]

I get 1. There is 1 occurrence.

I pasted your new data to my spreadsheet. My data goes like below. [For mercy's sake, I eliminated many rows here; I'm sure you follow.]

	A	B	C	D
Date and Time	Patient ID	Glucose	>180 2x in a day
A
A

<tbody>
[TD="align: center"]35[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]1/5/2017 3:35[/TD]

[TD="align: right"]189[/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]

[TD="align: center"]73[/TD]
[TD="bgcolor: #FFFF00"]Results Expected:[/TD]
[TD="bgcolor: #FFFF00"]>180 2x in a Day for =>3 consecutive days[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]74[/TD]
[TD="bgcolor: #FFE699"]Patient[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFE699"]# Occurrences[/TD]
[TD="align: right"][/TD]

[TD="align: center"]75[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"]1[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet14​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D36[/TH]
[TD="align: left"]{=SUM((B36=$B$36:$B$71)*(INT(A36)=INT($A$36:$A$71))*($C$36:$C$71>180))>=2}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C75[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(($D$36:$D$71=TRUE)*($B$36:$B$71=A75),ROW($D$36:$D$71)),IF(($D$36:$D$71<>TRUE)*($B$36:$B$71=A75),ROW($D$36:$D$71)))>=6,1,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Sorry, my mistake. This keeps happening when I look through the Zero-reply posts - I read it and it's Excel and then it turns into Access later. Hmmmm,.

In the "Zero Reply Posts", if you look all the over to the right, it tells you which forum each thread is in.
Many people fail to notice that!

 

[xenou]

Question: if there are four consecutive days (say days Jan 1, Jan 2, Jan 3, and Jan 4) ...

do you count this as "2" since it's two sets of consecutive days {1,2,3} and {2,3,4}?
or do you count it as "1" since it's one set of consecutive days >= 3

 

[zcavaricci]

For my purposes (Jan{1,2,3,4}, {6,7,8,9,10},{27,28,29,30}) would be 3 occurrences.