Counting Consecutive Instances

C

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

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top