Hi Sam,

A simple working solution to your problem is to use a helper
column. With your sample data in A2:A40 then
=IF(A2=A1,B1+1,1) placed in B2 and dragged down to B40
and in C1 put =MAX(B2:B40)

That is a very simple workable solution to the problem that many
spreadsheet designers would use yet you don't want helper columns
and drag downs which leads me me to believe that you are trying to
solve a problem in a competition or the like.

If that is the case then do your own dirty work.

My apologies if my assumption is incorrect
Martin




"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:85cf16d429f75@uwe...
Hi All,

I have a single column, dynamic named range called "Data" that contains
numeric values.

Is it possible without the use of a helper column (or other fill down) to
provide a formula that can count the longest consecutive sequence of zeros
in
"Data" and return that count to a single cell.

Sample Data Layout:
1
2
0
0
1
2
3
0
1
0
1
2
0
0
0
0
1
2
0
0
1
2
0
1
2
0
0
0
0
0
0
0
0
0
1
0
0
1
0


Expected Result:
Longest consecutive sequence of zeros is 9.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200806/1