array question

  • Thread starter Thread starter Len Case
  • Start date Start date
L

Len Case

Hi all...I hope someone can help me here, I have a single array column just
over 100 long which is filled with noughts and ones and I would like to
count how many
noughts there are between each number of ones. The answer could be EG
5,8,3,9,2,4 which
tells me there is six gaps and the number of noughts in each gap.
thanks for any help
Len
 
Try this:

Assume the data starts in cell A1.

Enter this formula in B1:

=IF(AND(A1=0,A2=1),1,"")

Enter this formula in B2 and copy down to the end of data in column A. I'm
assuming there is an empty cell at the end of data in column A.

=IF(A2=1,"",IF(OR(A3=1,A3=""),COUNTIF(A$1:A2,0)-SUM(B$1:B1),""))
 
Back
Top