Counting Consecutive Instances

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

carl - 24 Sep 2007 18:58 GMT

My data table is like this:

Date    BOX
3-Jan    6.16%
4-Jan    5.87%
5-Jan    2.60%
8-Jan    5.89%
9-Jan    6.00%
10-Jan    6.00%
11-Jan    6.00%
12-Jan    2.00%

I am trying to add a ColumnC that will count the consecutive instances of
the value in ColB if it greater than or = 5%. Sort of like this:

Date    BOX    Count
3-Jan    6.16%    1
4-Jan    5.87%    2
5-Jan    2.60%    0
8-Jan    5.89%    1
9-Jan    6.00%    2
10-Jan    6.00%    3
11-Jan    6.00%    4
12-Jan    2.00%    0

Thank you in advance.

Reply to this Message

JE McGimpsey - 24 Sep 2007 19:04 GMT

One way:

C2: =--(B2>=5%)
C3: =IF(B3<5%,0,C2+1)

Copy C3 down as far as needed

> My data table is like this:
>
[quoted text clipped - 22 lines]
>
> Thank you in advance.

Reply to this Message

Ron Coderre - 24 Sep 2007 19:06 GMT

Using your posted data
try this:

C2: =(B2>=0.05)*(N(C1)+1)
copy that formula down as far as you need

Does that help?
***********
Regards,
Ron

XL2003, WinXP

> My data table is like this:
>
[quoted text clipped - 22 lines]
>
> Thank you in advance.

Reply to this Message

T. Valko - 24 Sep 2007 19:08 GMT

Enter this formula in C2:

=--(B2>=0.05)

Enter this formula in C3 and copy down as needed:

=IF(B3>=0.05,C2+1,0)

Signature

Biff
Microsoft Excel MVP

> My data table is like this:
>
[quoted text clipped - 22 lines]
>
> Thank you in advance.

Reply to this Message