Calculating the largest consecutive gap in a series of numbers

[Mjau]

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,

Dave

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=COUNTIF(INDEX(A2:A65536,MATCH(2,1/(A2:A65536=1))):INDEX(A2:A65536,MATCH(9.99999999999999E+307,A2:A65536)),0)

Note that if the number 1 does not occur at least once in the data, the formula will return #N/A. If this is the case, the formula would need to be modified.

Hope this helps!

xl-central.com

[Ron Coderre]

You posted this exact question in another forum WITHOUT letting either
group know about the other! That means if you receive an solution from one
group, the other group will be wasting their time on your solved problem,
when they could be helping others.

At least, post a link to the other forum so our members can keep
track of the progress there.

[Special-K]

This seems to work:

copy this into column B from B2 downwards

=IF(A2=1,0,IF(AND(A2=0,A1=0),B1+1,1))

Then in C2 use Ctrl-Shift-Enter to insert the formula

=INDEX(B2:B50,MAX((ABS(B2:B50)>0)*ROW(B2:B50))-(CELL("Row",B2)-1))