Formula for counting consecutive cells by column

[gruntingmonkey]

Hello, I'm looking to find the formula to calculate if a range of cells, by row, are consecutively completed more than 5 times. IE, if 5 columns are completed next to each other regardless of where that string is in the range, then it is true, if it is less that 5 for the range, then it is false.

I'm looking to get the formula for sell P2 in the picture below.

Thanks in advance for reviewing.

 

[hagia_sofia]

Do you mean something like this:

=MAX(SCAN(0,A2:O2,LAMBDA(a,b,IF(ISTEXT(b),a+1,0))))>=5

 

[gruntingmonkey]

Do you mean something like this:

=MAX(SCAN(0,A2:O2,LAMBDA(a,b,IF(ISTEXT(b),a+1,0))))>=5

Not something like this. Exactly this! Thank you!

 

[gruntingmonkey]

Follow up question to this: Instead of coming back with True/False is their a way to count the max cells that are filled in?

 

[hagia_sofia]

If you mean the max consecutive chain then just:

=MAX(SCAN(0,A2:O2,LAMBDA(a,b,IF(ISTEXT(b),a+1,0))))

 

[gruntingmonkey]

If you mean the max consecutive chain then just:

=MAX(SCAN(0,A2:O2,LAMBDA(a,b,IF(ISTEXT(b),a+1,0))))

Thank you again! I should have seen that...

 

[gruntingmonkey]

Another question.. Is there a way of returning the last column ref? IE Z or 26

Also, If there are more than 2 groups of consecutive cells filled in, will it just return the largest? Is there a way to show multiples. IE Z,AK,AY or 26,37,51

 

[shaowu459]

Another question.. Is there a way of returning the last column ref? IE Z or 26

Also, If there are more than 2 groups of consecutive cells filled in, will it just return the largest? Is there a way to show multiples. IE Z,AK,AY or 26,37,51

is this what you need?

工作簿1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Time1	Time2	Time3	Time4	Time5	Time6	Time7	Time8	Time9	Time10	Time11	Time12	Time13	Time14	Time15	Time16	Time17	Time18	Time19	Time20	Time21
2		m1	m2	m3	m4	m5		m1	m2			m1			m1	m2	m3	m4	m5		m6
3
4					6
5					19
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
E4:E5	E4	=TOCOL(DROP(GROUPBY(TOCOL(SCAN(0,A2:U2,LAMBDA(x,y,x+(y=0)))),TOCOL((A2:U2>0)*COLUMN(A1:U1)),LAMBDA(x,MAX(x)/(COUNT(0/x)>4)),,0),,1),3)
Dynamic array formulas.

 

[gruntingmonkey]

I'm getting a #Name error on that.

I would also need it run along one row rather than down a column.

 

[shaowu459]

maybe groupby is not available in your office version.
=TOrow(DROP(GROUPBY(TOCOL(SCAN(0,A2:U2,LAMBDA(x,y,x+(y=0)))),TOCOL((A2:U2>0)*COLUMN(A1:U1)),LAMBDA(x,MAX(x)/(COUNT(0/x)>4)),,0),,1),3)