+ Reply to Thread
Results 1 to 5 of 5

Counting consecutive values?

  1. #1
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    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.

    Many thanks in advance!

    Elijah
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Counting consecutive values?

    Please try at C11
    =MATCH(-SIGN(B11),INDEX(SIGN(B12:B99),),)

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

  3. #3
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    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.

    Many thanks in advance!
    Attached Files Attached Files

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Counting consecutive values?

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

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

  5. #5
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Counting consecutive values less than a .9
    By mckenziegolf in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-16-2017, 12:05 PM
  2. [SOLVED] (HELP) Counting Consecutive Values < 1 From Right
    By mus1ca in forum Excel General
    Replies: 6
    Last Post: 07-22-2014, 08:51 AM
  3. [SOLVED] Counting consecutive values less than X
    By stellards20 in forum Excel General
    Replies: 3
    Last Post: 06-18-2014, 12:08 AM
  4. Counting Consecutive values and saving them
    By tdrouillard in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-11-2013, 10:23 PM
  5. [SOLVED] Counting Consecutive Values
    By khauskins in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2012, 11:37 AM
  6. Counting consecutive values
    By Elijah in forum Excel General
    Replies: 7
    Last Post: 04-23-2010, 05:13 PM
  7. Counting consecutive non-zero values
    By RichH6109 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-26-2007, 07:21 PM

Bookmarks

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