# Counting consecutive values?

1. ## Counting consecutive values?

Hi all,

I have a basic spreadsheet with two columns, date and price change (%).

I'm interested in somehow tracking the # of consecutive similar values. I think the very best would be if there were cell formula that could simply count consecutive values for each single row. Like shown here:

I would imagine every new day/sequence starting with 1.

So, if it's the first positive day after a prior day down, it says 1. If the next day is positive, it says 2, if the 3rd day is positive, it says 3. Etc.

That way I could easily sort the table and find similar patterns and the associated dates.

Consecutive.PNG

Also attaching a workbook.

Elijah

2. ## Re: Counting consecutive values?

=MATCH(-SIGN(B11),INDEX(SIGN(B12:B99),),)

or
=MATCH(0,INDEX(-(SIGN(B11)=SIGN(B12:B99)),),)

3. ## Re: Counting consecutive values?

Wow!

That works great, Bo_Ry!

Thank you so much.

In your opinion - which of the two formulas should I choose?

Also, could the formula be adapted to work with text data? I have some other text data where I'd be interested in doing the same thing.

For simplicity, I just converted the numerical % values to text, Up or Down. Please see attached.

4. ## Re: Counting consecutive values?

For number
D11
=MATCH(0,INDEX(-(SIGN(B11)=SIGN(B12:B99)),),)

For Text
=MATCH(0,INDEX(-(C11=C12:C99),),)

5. ## Re: Counting consecutive values?

Thank you so much, Bo_Ry!

Works like a charm. I wish I could give you more reputation, but I can't yet.

Wishing you a nice day and putting this as solved!

PS: These index formulas really slow down the larger sheets. Maybe I'll have to work around that by actually copying the values to another table when doing queries and sorting the data.

Best regards,

Elijah

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1