Counting the largest number of consecutive 1's in a column

  • Thread starter Thread starter DavidS
  • Start date Start date
D

DavidS

Hello, I have a column with 1000+ rows, each row having a 0 or 1. I'm
looking for a formula that will give me the highest number of consecutive
1's in the entire column. Just in case I didn't explain this correctly, here
is an example with a column fragment laid sideways:
0101100001111000111110010101. In this example the formula was produce a
result of 5. I'm not sure if this is possible. Thanks for your help, David
 
How about a UDF?

Function FindMax(MyLetter As String, myRange _
As Range) As Integer
Dim C As Range, TempMax As Integer, _
fReset As Boolean
For Each C In myRange.Cells
If C.Value Like MyLetter Then
TempMax = TempMax + 1
Else
TempMax = 0
End If
FindMax = Application.WorksheetFunction _
.Max(FindMax, TempMax)
Next
End Function

=FindMax(1,A1:A1200)

If search item is text surround with double-quotes

e.g. =FindMax("a",range)


Gord Dibben MS Excel MVP
 
By formula...

=MAX(FREQUENCY(IF(A2:A1000=1,ROW(A2:A1000)),IF(A2:A1000<>1,ROW(A2:A1000))
))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
DavidS said:
Hello, I have a column with 1000+ rows, each row having a 0 or 1. I'm
looking for a formula that will give me the highest number of consecutive
1's in the entire column. Just in case I didn't explain this correctly,
here is an example with a column fragment laid sideways:
0101100001111000111110010101. In this example the formula was produce a
result of 5. I'm not sure if this is possible. Thanks for your help, David
Click to expand...

David

For a generic formula, which will look for any number or text entered in F1,
this array formula will do the job:

=MAX(FREQUENCY(IF(A1:A1000=F1,COUNTIF(OFFSET(A1,,,ROW(INDIRECT("1:"&ROWS(A1:A1000)))),"<>"&F1)),ROW(INDIRECT("1:"&ROWS(A1:A1000)))-1))

To be confirmed with <Shift><Ctrl><Enter>, also if edited later.
 
Back
Top