array question
 

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

array question
 

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),""))

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...
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

array question
 

Thankyou very much it did the trick great
thanks from Len


"T. Valko" wrote in message
...
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),""))

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...
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

array question
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...
Thankyou very much it did the trick great
thanks from Len


"T. Valko" wrote in message
...
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),""))

--
Biff
Microsoft Excel MVP


"Len Case" wrote in message
...
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