Count consecutive occurances

[administrator]

I'm looking lo count the number of consecutive occurances of a 1
without a zero in a table but only if it is a new run. i.e.

Data Result
0 0
1 1
0 0
1 0
1 0
1 3
0 0

 

[T. Valko]

Here's a kludge:

Enter a zero in B1

Enter this formula in B2 and copy down as needed:

=IF(A2=0,0,IF(A3<>A2,ROWS(B$1:B2)-IF(COUNT(B$1:B1),LOOKUP(2,1/ISNUMBER(B$1:B1),ROW(B$1:B1)),0),"0"))

Note that this formula will return both numeric 0's and *TEXT* 0's (that's
the kludge I mentioned!). Align the column right.

If that's not acceptable I can do this using a helper column with all
returns being numeric. Post back if you'd prefer that method.

Biff

 

[EV]

Thanks Biff,

This will work

Here's a kludge:

Enter a zero in B1

Enter this formula in B2 and copy down as needed:

=IF(A2=0,0,IF(A3<>A2,ROWS(B$1:B2)-IF(COUNT(B$1:B1),LOOKUP(2,1/ISNUMBER(B$1:­B1),ROW(B$1:B1)),0),"0"))

Note that this formula will return both numeric 0's and *TEXT* 0's (that's
the kludge I mentioned!). Align the column right.

If that's not acceptable I can do this using a helper column with all
returns being numeric. Post back if you'd prefer that method.

Biff







- Show quoted text -

 

[T. Valko]

I found a bug! If in your example A1 was also 1 the first interval count
would be incorrect. So, instead of entering a 0 in B1 enter this formula:

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

Biff

Thanks Biff,

This will work

 

[Harlan Grove]

Here's a kludge:

Enter a zero in B1

Enter this formula in B2 and copy down as needed:

=IF(A2=0,0,IF(A3<>A2,ROWS(B$1:B2)-IF(COUNT(B$1:B1),
LOOKUP(2,1/ISNUMBER(B$1:B1),ROW(B$1:B1)),0),"0"))

....

Why kludge?

With the OP's data in A1:A8 including the Data heading in row 1, and
since the data would contain only 1s and 0s,

B2:
=--(A2>N(A3))

B3 [array formula]:
=IF(A3>N(A4),ROW()-MATCH(2,1/(A$1:A3<>1)),0)

 

[T. Valko]

Why kludge?

That's the only thing I could think of at the time without using a helper
column.

B2:
=--(A2>N(A3))

B3 [array formula]:
=IF(A3>N(A4),ROW()-MATCH(2,1/(A$1:A3<>1)),0)

Very nice!

Biff

Here's a kludge:

Enter a zero in B1

Enter this formula in B2 and copy down as needed:

=IF(A2=0,0,IF(A3<>A2,ROWS(B$1:B2)-IF(COUNT(B$1:B1),
LOOKUP(2,1/ISNUMBER(B$1:B1),ROW(B$1:B1)),0),"0"))

...

Why kludge?

With the OP's data in A1:A8 including the Data heading in row 1, and
since the data would contain only 1s and 0s,

B2:
=--(A2>N(A3))

B3 [array formula]:
=IF(A3>N(A4),ROW()-MATCH(2,1/(A$1:A3<>1)),0)

 

[ryanpoth]

With the OP's data in A1:A8 including the Data heading in row 1, and
since the data would contain only 1s and 0s,

B2:
=--(A2>N(A3))

B3 [array formula]:
=IF(A3>N(A4),ROW()-MATCH(2,1/(A$1:A3<>1)),0)

Whats wrong with:

=(SUM($A$2:A3)-SUM($B$2:B2))*AND(A3=1,A4=0)

in B3 (not array-entered)?

Also, I'm not sure why you're using N() since the OP made no reference
to text values, and when I tested your formula, it was out by 1 (ie on
a count of 3 occurences, it counted 4).

 

[ryanpoth]

With the OP's data in A1:A8 including the Data heading in row 1, and
since the data would contain only 1s and 0s,

B2:
=--(A2>N(A3))

B3 [array formula]:
=IF(A3>N(A4),ROW()-MATCH(2,1/(A$1:A3<>1)),0)

Whats wrong with:

=(SUM($A$2:A3)-SUM($B$2:B2))*AND(A3=1,A4=0)

in B3 (not array-entered)?

Also, I'm not sure why you're using N() since the OP made no reference
to text values, and when I tested your formula, it was out by 1 (ie on
a count of 3 occurences, it counted 4).

or even

=(SUM($A$2:A3)-SUM($B$2:B2))*(A3>A4)

 

[T. Valko]

Harlan's formulas work just fine.

As far as N(), knowing Harlan, he's being thorough!

Your formula also works if all the entries are numbers (0,1)

Biff

With the OP's data in A1:A8 including the Data heading in row 1, and
since the data would contain only 1s and 0s,

B2:
=--(A2>N(A3))

B3 [array formula]:
=IF(A3>N(A4),ROW()-MATCH(2,1/(A$1:A3<>1)),0)

Whats wrong with:

=(SUM($A$2:A3)-SUM($B$2:B2))*AND(A3=1,A4=0)

in B3 (not array-entered)?

Also, I'm not sure why you're using N() since the OP made no reference
to text values, and when I tested your formula, it was out by 1 (ie on
a count of 3 occurences, it counted 4).

 

[Harlan Grove]

(e-mail address removed) wrote...

B2:
=--(A2>N(A3))

B3 [array formula]:
=IF(A3>N(A4),ROW()-MATCH(2,1/(A$1:A3<>1)),0)

Whats wrong with:

=(SUM($A$2:A3)-SUM($B$2:B2))*AND(A3=1,A4=0)

in B3 (not array-entered)?

Nothing, but it's better to use an enclosing IF to avoid calculating
the SUMs when A3<=A4.

Also, I'm not sure why you're using N() since the OP made no
reference to text values, . . .

Defensiveness. The bottommost formula would refer to a cell one row
below the bottom of the data. I've learned not to assume such cells
are blank. Still, it'd better to drop the N calls and use a different
formula in the bottommost row.

. . . and when I tested your formula, it was out by 1 (ie on
a count of 3 occurences, it counted 4).

That's because your data started in row 3 rather than row 3. My
formula would be 2 off if the data started in row 4, etc.

 

[Robert Dunham]

I'm looking lo count the number of consecutive occurances of a 1
without a zero in a table but only if it is a new run. i.e.

Data Result
0 0
1 1
0 0
1 0
1 0
1 3
0 0

Suppose you wanted this instead

Data Result