Formula to count the number of sets of 7-consecutive-zero's in a row of data

[Cynthia Osorio Florez]

I have a database with 300 rows and 50 columns. The cells are populated with either zero, one or blank. I'm looking for a formula that I can plug into column number 51 for the 300 rows, that will tell me how many sets of 7-consecutive-zero's there are in each row.

 

[Cynthia Osorio Florez]

Let me further clarify, it should count the sets of at least 7 OR MORE consecutive zero's. i.e. If there is a set of 8 zero's this set should be counted as one, not two, sets. If there is a set of 14 consecutive zero's, this should be counted as one set, not two.

I have a database with 300 rows and 50 columns. The cells are populated with either zero, one or blank. I'm looking for a formula that I can plug into column number 51 for the 300 rows, that will tell me how many sets of 7-consecutive-zero's there are in each row.

 

[XOR LX]

Hi,

Based on that data being in A1:AX300, in AY1, array formula**:

=COUNT(1/(FREQUENCY(IF(A1:AX1=0,COLUMN(A1:AX1)),IF(A1:AX1<>0,COLUMN(A1:AX1)))>6))

Copy down as required.

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).

 

[Marcelo Branco]

Assuming data in A2:AX2 try this array formula
=SUM(IF(FREQUENCY(IF((A2:AX2<>"")*(A2:AX2=0),COLUMN(A2:AX2)),IF(1-(A2:AX2<>"")*(A2:AX2=0),COLUMN(A2:AX2)))>=7,1))

confirmed with Ctrl+Shift+Enter, not just Enter

copy down

@XOR LX
As there are blank cells, i *think* is necessary to include the condition (A1:AX1<>"") in your formula.

M.

 

[XOR LX]

@XOR LX
As there are blank cells, i *think* is necessary to include the condition (A1:AX1<>"") in your formula.

Hi, Marcelo.

Good point. I assumed that these "blanks" were null strings (""), in which case my formula is fine. If there are actually "genuine" blanks then, yes, my formula will fail.

Regards

 

[XOR LX]

@Marcelo

We could also use EXACT, so as to perform just a single check:

=COUNT(1/(FREQUENCY(IF(EXACT(A1:AX1,0),COLUMN(A1:AX1)),IF(1-EXACT(A1:AX1,0),COLUMN(A1:AX1)))>6))

Regards

 

[Marcelo Branco]

@XOR LX

Yes, EXACT works as desired

M.