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:

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

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Results Expected:

Patient	# Occurrences
A	1
C	1

<tbody>
</tbody>

 

[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
1	Date/Time	Patient ID	Glucose Reading	>180 2x in a day
2	1/28/2017 17:36	A	192	TRUE
3	1/28/2017 20:12	A	311	TRUE
4	1/29/2017 9:58	A	214	TRUE
5	1/29/2017 10:58	A	181	TRUE
6	1/30/2017 20:45	A	279	TRUE
7	1/30/2017 21:45	A	181	TRUE
8	1/31/2017 7:48	A	157	FALSE
9	1/31/2017 12:33	A	157	FALSE
10	1/31/2017 17:48	A	167	FALSE
11	1/28/2017 12:35	B	158	FALSE
12	1/29/2017 12:09	B	190	TRUE
13	1/29/2017 17:05	B	211	TRUE
14	1/30/2017 7:41	B	197	TRUE
15	1/30/2017 12:05	B	211	TRUE
16	1/30/2017 17:31	B	231	TRUE
17	1/31/2017 21:26	B	188	FALSE
18	1/5/2017 22:05	C	205	TRUE
19	1/5/2017 23:40	C	216	TRUE
20	1/6/2017 3:18	C	195	TRUE
21	1/6/2017 7:54	C	187	TRUE
22	1/7/2017 12:54	C	210	TRUE
23	1/7/2017 17:22	C	379	TRUE
24	1/9/2017 7:37	C	191	FALSE
25
26
27	Results Expected:	>180 2x in a Day for =>3 consecutive days
28	Patient		# Occurrences
29	A		1
30	B		0
31	C		1

<tbody>
</tbody>

Sheet14​

Array Formulas Cell Formula D2 {=SUM((B2=$B$2:$B$24)*(INT(A2)=INT($A$2:$A$24))*($C$2:$C$24>180))>=2} C29 {=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))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

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

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

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

 

[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
35	Date and Time	Patient ID	Glucose	>180 2x in a day
36	1/5/2017 3:35	A	189	FALSE
73	Results Expected:	>180 2x in a Day for =>3 consecutive days
74	Patient		# Occurrences
75	A		1

<tbody>
</tbody>

Sheet14​

Array Formulas Cell Formula D36 {=SUM((B36=$B$36:$B$71)*(INT(A36)=INT($A$36:$A$71))*($C$36:$C$71>180))>=2} C75 {=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))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

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