Cumulative Count Check

[Steveym]

Hello, please see attached example sheet.

I have a data listing as per column A 'Point' and a classification column as per column B.

I would like an output as shown in column C 'final classification' which I have pre-populated. The output should pick a cumulative count from column B if it meets the criteria shown in cell F2.

In laymans terms there are five consecutive 'OK' values based on the number input to column F2 therefore the final classification in column C is 'OK'.

Many thanks,

Steven

 

[JamesCanale]

I'm hoping you have some kind of mistake in your example from rows 5 to 14, because otherwise I can't understand it at all.

MrExcelPlayground19.xlsx
	A	B	C	D	E	F
1	Point	Classification	Final Classification
2	1	OK	Not OK		Cumulative criteria	5
3	2	Not OK	Not OK
4	3	OK	Not OK
5	4	OK	Not OK
6	5	Not OK	Not OK
7	6	Not OK	Not OK
8	7	Not OK	Not OK
9	8	Not OK	Not OK
10	9	Not OK	Not OK
11	10	OK	OK
12	11	OK	OK
13	12	OK	OK
14	13	OK	OK
15	14	OK	OK
16	15	Not OK	Not OK
17	16	Not OK	Not OK
18	17	OK	Not OK
19	18	OK	Not OK
20	19	Not OK	Not OK
21	20	Not OK	Not OK
22	21	Not OK	Not OK
23	22	Not OK	Not OK
24	23	Not OK	Not OK
25	24	Not OK	Not OK
26	25	Not OK	Not OK
27	26	OK	OK
28	27	OK	OK
29	28	OK	OK
30	29	OK	OK
31	30	OK	OK
32	31	Not OK	Not OK
33	32	Not OK	Not OK
34	33	Not OK	Not OK
35	34	OK	Not OK
36	35	OK	Not OK
37	36	OK	Not OK
38	37	OK	Not OK
39	38	Not OK	Not OK
40	39	Not OK	Not OK
41	40	OK	OK
42	41	OK	OK
43	42	OK	OK
44	43	OK	OK
45	44	OK	OK
46	45	Not OK	Not OK
47	46	Not OK	Not OK
48	47	OK	Not OK
49	48	OK	Not OK
50	49	OK	Not OK
51	50	OK	Not OK
Sheet10

Cell Formulas
Range	Formula
C2:C51	C2	=LET(z,$F$2,a,$B$2:$B$51,b,A2,c,MAX($A$2,A2-z+1),d,MIN($A$51,A2+z-1),e,INDEX(a,SEQUENCE(d-c+1,1,c))="OK",f,MAX(FREQUENCY(IF(e,SEQUENCE(ROWS(e))),IF(NOT(e),SEQUENCE(ROWS(e))))),g,f>=z,IF(g,"OK","Not OK"))

 

[Steveym]

My bad, example should be as attached.

 

[JamesCanale]

Okay, my formula up there should work then.

 

[Steveym]

Okay, my formula up there should work then.

Fantastic many thanks

 

[Steveym]

Okay, my formula up there should work then.

I am having a bit of an issue with the formula when I have added it to my dataset and changed a few of the values to my working criteria.
Can you see any stand-out issue with the attached?

 

[JamesCanale]

C1 has to be an integer for this thing to work - it's the number of consecutive things to look for (5 in the original example). The whole thing was setup for column A to be sequential integers.