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

 

[gaz_chops]

Oops sorry should have checked back!

 

[vitaalijs]

Does it change something?

 

[gaz_chops]

Check Dropbox I inserted a row at the top with the formula in.

 

[vitaalijs]

Thank you very much!!!

 

[gaz_chops]

You're welcome.

 

[Aladin Akyurek]

If you don't to read empty cells as 0's, you need to make a more expensive formula:

Control+shift+enter...

=MAX(FREQUENCY(IF(ISNUMBER(A2:A17),IF(A2:A17=0,ROW(A2:A17))),IF(ISNUMBER(1/A2:A17),ROW(A2:A17))))

 

[gaz_chops]

If you don't to read empty cells as 0's, you need to make a more expensive formula:

Control+shift+enter...

=MAX(FREQUENCY(IF(ISNUMBER(A2:A17),IF(A2:A17=0,ROW(A2:A17))),IF(ISNUMBER(1/A2:A17),ROW(A2:A17))))

Hi Aladin, is that a better option than
=MAX(FREQUENCY(IF(A2:A25=0,IF(A2:A25<>"",ROW(A2:A25))),IF(A2:A25<>0,ROW(A2:A25))))

Gaz

 

[Aladin Akyurek]

Hi Aladin, is that a better option than
=MAX(FREQUENCY(IF(A2:A25=0,IF(A2:A25<>"",ROW(A2:A25))),IF(A2:A25<>0,ROW(A2:A25))))

Gaz

A true 0 is a number; the ISNUMBER test captures that. Other than that, I have no data on the temporal score of the formula to offer.

 

[gaz_chops]

A true 0 is a number; the ISNUMBER test captures that. Other than that, I have no data on the temporal score of the formula to offer.

Thanks

 

[vitaalijs]

Thanks

A true 0 is a number; the ISNUMBER test captures that. Other than that, I have no data on the temporal score of the formula to offer.

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!