Check if series of values exist within a row array

[tpcdoyle]

Hi forum!

I don't believe I've posted on this before, however I've been stumped on this problem for a few days and any insight would be immensely appreciated !

I have a 3 week moving attendance data set which I update/audit weekly. There's 2 conditions which I audit for: (a) if an employee works a full week, do they at least have one day off? -or- (b) if an employee works 2 consecutive full weeks, do they have at least 2 consecutive days off? - the latter is the one I'm stumped on!!

The data set:

Emp#/D.O.M.	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	(a)	(b)
1234	1	1	1	1	1	1	1	0	1	1	1	1	1	1	1	0	1	1	1	1	1	TRUE	TRUE
1235	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	1	1	1	1	1	FALSE	TRUE
1236	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	FALSE	FALSE

<tbody>
</tbody>

Column W (a) checks for the first condition (which I have solved) however column X (b) is supposed to do check for the second condition; the first row (D.O.M.) contains the day of the month and the first column contains the employee number.

I have pseudo-code written for essentially what I'd like for output:

IF Series {1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0} Exists in Row Range (B2:V2) THEN TRUE ELSE FALSE etc.

I think that's all - let me know if I need to provide any additional info.

Thanks!

 

[Eric W]

Welcome to the forum!

I have 2 options for you. Second option might be the best.

X2: =AND(MAX(FREQUENCY(IF(B2:V2=1,COLUMN(B2:V2)),IF(B2:V2=0,COLUMN(B2:V2))))>=14,MAX(FREQUENCY(IF(B2:V2=0,COLUMN(B2:V2)),IF(B2:V2=1,COLUMN(B2:V2))))>=2)
confirmed with Control+Shift+Enter.

This checks for a run of 14 1's, and a run of 2 0's, but not necessarily adjacent.

X2: =IF(SUM((SUBTOTAL(9,OFFSET(B2:O2,0,ROW(INDIRECT("1:6"))-1,1,14))=14)*(SUBTOTAL(9,OFFSET(B2:O2,0,ROW(INDIRECT("1:6"))+13,1,2))=0)),TRUE)
also with Control+Shift+Enter.

This is closer. It looks for a run of 14 1's, followed by 2 cells summing to 0.

Hope this helps!

 

[pgc01]

Hi
Welcome to the board

Please post your excel version.

Remark:
When you post it helps if you post the excel version that you are using so that we know what you have available.
For ex., in this case, if you are using the later versions of excel this would be an easy task.