How can I find the largest sequence of a given number in excel?
I have a column of zeros and ones
1 0 0 0 1 1
I want to find out the largest sequence of zeros I have in my column AND how many times it appears.
With the data in column A in B1 enter:
=IF(A1=1,0,1)
and in B2 enter:
=IF(A2=0,1+B1,0)
and copy:
longest sequence:
=MAX(B:B)
and the number of times it has is as follows:
=COUNTIF(B:B,D1)
if formula MAX () is in D1
If you want to avoid the helper column, you can use this "array formula" in C1 for maximum consecutive zeros, counting the data in A2: A100
=MAX(FREQUENCY(IF((A2:A100=0)*(A2:A100<>""),ROW(A2:A100)),IF(A2:A100=1,ROW(A2:A100))))
And this formula in C2 for the number of instances
=SUM(IF(FREQUENCY(IF((A2:A100=0)*(A2:A100<>""),ROW(A2:A100)),IF(A2:A100=1,ROW(A2:A100)))=C1,1))
Both formulas must be validated with CTRL+ SHIFT+ENTER
I am assuming you will have continuous data from A2 with only spaces at the end, maybe