Calculating largest gap in a series of consecutive numbers

[Samuel]

Hiya,

I am currently trying to calculate the largest gap in a series of numbers
where a particular number has not appeared and display this number in a cell.

For example,

There are 2 numbers involved, 0 and 1. My spreadsheet looks like:

A1
A2 1
A3 0
A4 0
A5 0
A6 0
A7 1
A8 0
A9 0 and so on.....

Now, in cell C1 I want excel to automatically calculate the number of times
0 has come up consecutively up to the last time the number 1 appeared. So
when inputting data downwards in A10, A11, A12 it will be able to tell me how
many consecutive 0's there have been until the last number 1 appeared.

In the above example, if I had input the data upto A5, C1 would display the
number 3 (there have been three 0's since the last 1 came up). When A6 is
filled in with a 0, C1 would display 4, yet once A7 had been filled in the
counter would reset back to 0 in C1.

I have tried to explain this as best as I can, if you need any clarification
please ask, and thank you kindly in advance,

 

[Ron Coderre]

With possible values in A2:A30

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
(instead of just ENTER) returns the maximum consecutive occurrences of zero.
C1: =MAX(FREQUENCY(IF((A2:A30<>"")*(A2:A30=0),ROW(A2:A30)),
IF(A2:A30<>0,ROW(A2:A30))))

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Mike H]

Samual,

I could manage to count the zeros with a formula but can't do the reset so
here's a VB solution. Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Or _
Target.Column <> 1 Then Exit Sub
Dim ScoreRange As Range
Set ScoreRange = Range("C1")
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For X = lastrow To 2 Step -1
If IsNumeric(Cells(X, 1)) And Cells(X, 1).Value = 0 Then
zeroes = zeroes + 1
Else
Application.EnableEvents = False
ScoreRange = zeroes
Application.EnableEvents = True
Exit For
End If
Next
End Sub

Mike

 

[Mike H]

Slight addition,

Put this line at the start of the code

zeroes = 0

Mike

 

[Bernd P]

Hello,

If I understand the question correctly, enter normally into C1
=MAX(0,LOOKUP(2,1/("0"=""&A2:A30),ROW(A2:A30))-LOOKUP(2,1/
(1=A2:A30),ROW(A2:A30)))

Regards,
Bernd

 

[Ron Coderre]

I think I misunderstood what you're looking for.
The formula I posted calculates the maximum consecutive occurrences of zero.
Not the count of consecutive zeros AFTER the last 1.
Bernd P seems to have gotten it right.

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Dave]

Thank you all very much! This has really helped me a lot!