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


On Thu, 16 Nov 2006 22:26:14 -0000, "DavidS" wrote:

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

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!

In article ,
"DavidS" wrote:

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

"DavidS" skrev i en meddelelse
...
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.

--
Best regards
Leo Heuser

Followup to newsgroup only please.