Counting sequential zeroes in column

Hi All,

Having a bit of a of a "hair tearing out" problem.

How do i go about having a formula which will return the number of times 0 is repeated in a column sequentially?

e.g.:

0
1
0
0
0
0
0
1
0
0
0

The idea being that after row 7 (there have been 5 consecutive zeroes), the count would reset when it hits a value greater than zero, and then count again once it hits a zero again.
Output for the example above should be 3.

Any suggestions? Thanks in advance!

NOt sure if the punctuation is incorrect or whether im entering it in wrong, but im getting an error if i enter it like that, and if i replace thes semi colon with a comma, then i just get the same values repeated.
And a sum of that does not give me the number of consecutive zeroes in the column.

Originally Posted by Miroslav R.

Hi there,

easy task when You are able to use help column.
Lets say Your sequence is in column A.
Then if cell A1 = 0 put 1 in cell B1, otherwise B1 = 0.
From B2 down fill formula:

Formula:

Please Login or Register  to view this content.

And make SUM for column B.

Hi XOR LX,

Thanks all for the fast replies. I haven't got that to work either im afraid.
My data starts at A2, and continues to the bottom of the sheet. Even when i enter the code you had though, again, it only seem to sum the numbers i had in that column. Not what i need either.

Originally Posted by XOR LX

Hi,

Assuming your entries are numerical and only ever zero or one, as in your example, use this array formula**:

=SUM(0+(FREQUENCY(IF(1-A1:A11,ROW(A1:A11)),IF(A1:A11,ROW(A1:A11)))>0))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Ah i see now what that formula does Miroslav. Sorry, crossed wires here id imagine. What your formula does is give me the number of times there were sequential 0's together.

What im looking for is how many zeroes are there in sequence as per the last entries in the workbook.
The function im using is for counting batches where no fail results were observed, so once it reaches 5, the test level can drop. So i need to know, as per the last entries, how many batches have had 0 failures.

Hope that clarifies things!

Originally Posted by Miroslav R.

oh... sorry for that...
You work with 2010, so yes = replace ';' with ','.

See attachement

Attachment 319760

Sorry, within reason obviously. lets say A400 for arguments sake.

as i said to Miroslav:

"What im looking for is how many zeroes are there in sequence as per the last entries in the workbook.
The function im using is for counting batches where no fail results were observed, so once it reaches 5, the test level can drop. So i need to know, as per the last entries, how many batches have had 0 failures."

Originally Posted by XOR LX

That's over a million rows' worth of data!?!!

All the feedback and responses are great, but i still havent got the output im looking for.

Heres what i have (1) number (2) is what i entered. (3) was the result. (4) is what i want to display (i manually entered it)
1.PNG2.PNG3.PNG4.PNG

Originally Posted by sktneer

You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.

Please Login or Register  to view this content.

The formula was suggested by XOR LX. I just made some changes in it to suit your requirement.

Excellent! Thanks sktneer! Just what i was looking for!

Appreciate it! I owe you one!

Originally Posted by sktneer

Find the attachment to see if this is what you want.