Counting Consecutive Instances

[carl]

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.

 

[JE McGimpsey]

One way:

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

Copy C3 down as far as needed

 

[Ron Coderre]

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

 

[T. Valko]

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)