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

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

wrote in message
ups.com...
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

Thanks Biff,

This will work



On Mar 15, 2:08 pm, "T. Valko" wrote:
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

wrote in message

ups.com...



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- Hide quoted text -

- Show quoted text -

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

"EV" wrote in message
ups.com...
Thanks Biff,

This will work



On Mar 15, 2:08 pm, "T. Valko" wrote:
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

wrote in message

ups.com...



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- Hide quoted text -

- Show quoted text -

"T. Valko" wrote...
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:
=--(A2N(A3))

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

Why kludge?

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

B2:
=--(A2N(A3))

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

Very nice!

Biff

"Harlan Grove" wrote in message
oups.com...
"T. Valko" wrote...
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:
=--(A2N(A3))

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

On Mar 15, 4:48 pm, "Harlan Grove" wrote:

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:
=--(A2N(A3))

B3 [array formula]:
=IF(A3N(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).

On Mar 16, 1:32 pm, wrote:
On Mar 15, 4:48 pm, "Harlan Grove" wrote:



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:
=--(A2N(A3))

B3 [array formula]:
=IF(A3N(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))*(A3A4)

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

wrote in message
oups.com...
On Mar 15, 4:48 pm, "Harlan Grove" wrote:

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:
=--(A2N(A3))

B3 [array formula]:
=IF(A3N(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).

wrote...
"Harlan Grove" wrote:
....
B2:
=--(A2N(A3))

B3 [array formula]:
=IF(A3N(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.

On 14 Mar 2007 18:35:23 -0700, wrote:

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
0 0
1 1
0 0
1 1
1 2
1 3
0 0