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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DavidS - 16 Nov 2006 22:26 GMT

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

Reply to this Message

Gord Dibben - 16 Nov 2006 22:41 GMT

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

>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

Reply to this Message

Domenic - 16 Nov 2006 23:37 GMT

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!

> 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

Reply to this Message

Leo Heuser - 17 Nov 2006 11:19 GMT

> 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

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.

Signature

Best regards
Leo Heuser

Followup to newsgroup only please.

Reply to this Message