Count max consecutive number 0 in each column

[vitaalijs]

I use Excel 2013 and I want to count biggest number of appearances of number 0 in each column. There is more than 12000 rows in each column. Is it possible?

For example, if in column there is numbers 1 0 0 0 0 0 0 0 1 1 0 0 0 0 1
I want to get result 7


My file is here
https://www.dropbox.com/s/nlp87pz5inh229s/jautajumam.xlsx?dl=0

 

[Aladin Akyurek]

Hi!

Thank you again for help that time ago. How I can get from this formula all these numbers of series of consecutive "0" from which this "MAX" had taken in formula for which I asked before?
For example if there is column 0 1 0 0 1 0 0 0 1 0 0 0 0 0 1
I would like to get result 1;2;3;5
Thank you in advance!

What is the range exactly?

 

[vitaalijs]

There 62 columns https://www.dropbox.com/s/3f9yv8zkcxn6v1s/62columns.xlsx?dl=0
Last row is 13214
If it is possible, I would like to get this information in top row like it was with formula before or in any other place if it easier.

 

[Aladin Akyurek]

There 62 columns https://www.dropbox.com/s/3f9yv8zkcxn6v1s/62columns.xlsx?dl=0
Last row is 13214
If it is possible, I would like to get this information in top row like it was with formula before or in any other place if it easier.

I wasn't asking for a file. Rather is this 0 1 0 0 1 0 0 0 1 0 0 0 0 0 1 envisioned to be in

A2:O2, an horiozontal

or in

A2:A16, a vertical range?

0 1 0 0 1 0 0 0 1 0 0 0 0 0 1

 

[vitaalijs]

In vertical range A2:A13214

 

[Aladin Akyurek]

In vertical range A2:A13214

Row\Col	A​	B​	C​
1​			4​
2​	0​
3​	1​		1​
4​	0​		2​
5​	0​		3​
6​	1​		5​
7​	0​
8​	0​
9​	0​
10​	1​
11​	0​
12​	0​
13​	0​
14​	0​
15​	0​
16​	1​

In C1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$17),IF($A$2:$A$17=0,ROW($A$2:$A$17))),
    IF(ISNUMBER(1/$A$2:$A$17),ROW($A$2:$A$17))),1))

In C3, control+shif+enter and copy down:

=IF(ROWS($C$3:C3)<=$C$1,SMALL(FREQUENCY(IF(ISNUMBER($A$2:$A$17),IF($A$2:$A$17=0,ROW($A$2:$A$17))),
    IF(ISNUMBER(1/$A$2:$A$17),ROW($A$2:$A$17))),ROWS($C$3:C3)+1),"")

 

[vitaalijs]

Thank you for your time, but something doesnt work. Can you take a look on my example? https://www.dropbox.com/s/r26umk1fjnpqfvt/test.xlsx?dl=0
It returns 0 in C3

 

[Aladin Akyurek]

Thank you for your time, but something doesnt work. Can you take a look on my example? https://www.dropbox.com/s/r26umk1fjnpqfvt/test.xlsx?dl=0
It returns 0 in C3

I think I know where/why the trouble occurs...

Row\Col	A​	B​	C​
1​	data		count of consecutive 0's​
2​	0​		4​
3​	1​		size list of consecutive 0's​
4​	1​		1​
5​	0​		1​
6​	1​		3​
7​	0​		5​
8​	0​		​
9​	0​
10​	1​
11​	0​		​
12​	0​
13​	0​
14​	0​
15​	0​
16​	1​
17​

Let A2:A17 of Sheet1 house the data.

Let's define (1) CONSEC in Formulas | Name Manager as referring to:

=FREQUENCY(IF(ISNUMBER(Sheet1!$A$2:$A$17),IF(Sheet1!$A$2:$A$17=0,
   ROW(Sheet1!$A$2:$A$17))),IF(ISNUMBER(1/Sheet1!$A$2:$A$17),ROW(Sheet1!$A$2:$A$17)))

and (2) IVEC as referring to:

=ROW(INDIRECT("1:"&SUM(ISNUMBER(CONSEC)+0)))

The foregoing definitions are really needed for reasons of efficiency.

In C2 control+shift+enter, not just enter:

=SUM(IF(CONSEC,1))<strike></strike>

In C4 control+shift+enter and copy down:

=IF(ROWS($C$4:C4)<=$C$2,INDEX(CONSEC,SMALL(IF(CONSEC,IVEC),ROWS($C$4:C4))),"")

Note. A huge range may yield a lots of 1's, 2's, etc. Instead of listing such one by one, we could create a table showing of frequencies of didtinct chunk sizes.

 

[vitaalijs]

Thank you for reviewing.
Now there is no result in C4 even if leave only 17 rows - https://www.dropbox.com/s/9mq8tk85z245usx/1col.xlsx?dl=0

And it`s great idea about creating a table. That`s the thing I really need the most and was thinking about making one after getting these results, so it will be great if it could be get with some formula.

 

[Aladin Akyurek]

Thank you for reviewing.
Now there is no result in C4 even if leave only 17 rows - https://www.dropbox.com/s/9mq8tk85z245usx/1col.xlsx?dl=0

And it`s great idea about creating a table. That`s the thing I really need the most and was thinking about making one after getting these results, so it will be great if it could be get with some formula.

Try to rebuild the set up I described. Do not forget required definitions.

 

[vitaalijs]

Thank you for correcting my inattention mistake. And much bigger thanks for the formula. I really appreciate your help.