Clever Geek Handbook

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.

+3


source to share


2 answers


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:

enter image description here



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

+3


source


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

+2


source






More articles:


All Articles