How to find largest sequence of a given number in excel?

Question

I have a column of zeros and ones

I want to find out the largest sequence of zeros I have in my column AND how many times it appears.

Gary's Student · Accepted Answer · 2015-06-04T03:09:20.407

4

With data in column A, in B1 enter:

=IF(A1=1,0,1)

and in B2 enter:

=IF(A2=0,1+B1,0)

and copy down:

enter image description here

the longest sequence is:

=MAX(B:B)

and the number of times it occurs is:

=COUNTIF(B:B,D1)

if the MAX() formula is in D1

edited Jun 04 '15 at 03:09

answered Jun 04 '15 at 01:32

Gary's Student

I would add that you can also filter on A for 0s and then on B and choose the maximum value. This will show you the places where the maximum occurs. – Gordon Linoff Jun 04 '15 at 02:14
@GordonLinoff Very good idea!..............We could also use *VLOOKUP()* to find that first maximum. – Gary's Student Jun 04 '15 at 02:21

barry houdini · Answer 2 · 2015-06-04T11:44:10.627

If you want to avoid a helper column you can use this "array formula" in C1 for the maximum consecutive zeroes, assuming 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 need to be confirmed with CTRL+SHIFT+ENTER

I'm assuming you will have continuous data from A2 with only blanks at the end, perhaps

2 Answers2