Count Longest Consecutive Sequence of zeros

[Sam via OfficeKB.com]

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

 

[Mike H]

Hi,

Try this array entered with CTRL+Shift+Enter

=MAX(FREQUENCY(IF(A1:A30=0,COLUMN(A1:A30)),IF(A1:A30<>0,COLUMN(A1:A30))))

Note if you have blanks they will evaluate as zero

Mike

 

[Mike H]

Ignore that it doesn't work!!

Hi,

Try this array entered with CTRL+Shift+Enter

=MAX(FREQUENCY(IF(A1:A30=0,COLUMN(A1:A30)),IF(A1:A30<>0,COLUMN(A1:A30))))

Note if you have blanks they will evaluate as zero

Mike

 

[Dave]

Hi,
One way:
If your given data is A1:A39
In B1 enter =IF(A1=0,1,"")
In B2 enter =IF(AND(ISNUMBER(B1),A2=0),B1+1,IF(A2=0,1,""))
Select B2 and copy down to the end of your data.
Then in another cell:
=MAX(B1:B39)

Regards - Dave.

 

[MartinW]

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

 

[Dave]

Oops, sorry. Mis-read your requirements. Thought you said "With use of helper
column..."
Regards - Dave.

 

[Tom Hutchins]

You could use a user-defined function like the following:

Public Function CountConsecZeros(RangeToCheck As Range) As Long
Dim c As Range, TmpCnt As Long, CurrMax As Long
If RangeToCheck.Columns.Count > 1 Then
MsgBox "RangeToCheck must be in a single column", , "Error"
CountConsecZeros = -1
Exit Function
End If
CurrMax = 0
TmpCnt = 0
For Each c In RangeToCheck
Select Case c.Value
Case 0
TmpCnt = TmpCnt + 1
Case Else
If TmpCnt > CurrMax Then
CurrMax = TmpCnt
End If
TmpCnt = 0
End Select
Next c
CountConsecZeros = CurrMax
End Function

Paste this function in a VBA module in your workbook. It is called like this:
=CountConsecZeros(A1:A39)

If you are new to user-defined functions (macros), this link to Jon
Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[RagDyeR]

Try this *array* formula for the example you posted, using Column A:

=MAX(FREQUENCY(IF(A1:A39=0,ROW(1:39)),IF(A1:A39<>0,ROW(1:39))))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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

 

[Sam via OfficeKB.com]

Hi RD,

Thank you very much for your time and assistance. Your formula has done the
job Brilliantly!

I replaced the A1 cell referencing with my named range.

Very much appreciated.

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Tom,

Thank you very much for your time and assistance. I have gone with RD's
solution but your user-defined function will most definitely be of use. Thank
you again for taking the time.

Cheers,
Sam

You could use a user-defined function like the following:

Public Function CountConsecZeros(RangeToCheck As Range) As Long
Dim c As Range, TmpCnt As Long, CurrMax As Long
If RangeToCheck.Columns.Count > 1 Then
MsgBox "RangeToCheck must be in a single column", , "Error"
CountConsecZeros = -1
Exit Function
End If
CurrMax = 0
TmpCnt = 0
For Each c In RangeToCheck
Select Case c.Value
Case 0
TmpCnt = TmpCnt + 1
Case Else
If TmpCnt > CurrMax Then
CurrMax = TmpCnt
End If
TmpCnt = 0
End Select
Next c
CountConsecZeros = CurrMax
End Function

Paste this function in a VBA module in your workbook. It is called like this:
=CountConsecZeros(A1:A39)

If you are new to user-defined functions (macros), this link to Jon
Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

 

[Sam via OfficeKB.com]

Hi Martin,

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.

I have multiple columns from which I need to extract the same type of
information and trying to avoid additional columns would be very helpful.

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

My apologies if my assumption is incorrect
Martin

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Mike,

Thank you for reply. RD just pipped you to it with the ROW Function.

Cheers,
Sam

 

[MartinW]

Hi Sam,

I am still intrigued as to why you would want to go with a slow
and cumbersome array solution when a simple and faster
helper column solution is available.

You say you have multiple columns, well, Excel has plenty of
columns to spare, there is no crime in using them, you just
hide the helpers.

Regards
Martin