+ Reply to Thread
Results 1 to 6 of 6

Counting consecutive repeated numbers greater than or less than.

  1. #1
    Registered User
    Join Date
    12-02-2014
    Location
    new brunswick, Canada
    MS-Off Ver
    2010
    Posts
    8

    Counting consecutive repeated numbers greater than or less than.

    Hi, new to the forum.
    I dumped in some random numbers generated from a bitcoin dice site called primedice.
    I'm trying to calculate the longest streaks of numbers less than 91 or above 9.
    All of my numbers are in one column, what is the best formula to achieve this?

    number dump:
    number dump.xlsx

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Counting consecutive repeated numbers greater than or less than.

    Using your posted workbook
    this regular formula returns the maximum count of consecutive values that are >=9 and <91
    Please Login or Register  to view this content.
    or
    This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
    Please Login or Register  to view this content.
    Both formulas return: 65

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    12-02-2014
    Location
    new brunswick, Canada
    MS-Off Ver
    2010
    Posts
    8

    Re: Counting consecutive repeated numbers greater than or less than.

    Hi, in my continued research I found this formula:
    =MAX(FREQUENCY(IF(A1:A58728>9,ROW(A1:A58728)),IF(A1:A58728<=9,ROW(A1:A58728))))
    =MAX(FREQUENCY(IF(A1:A58728<91,ROW(A1:A58728)),IF(A1:A58728>=91,ROW(A1:A58728))))
    This gave me longest run of 84 for under 9, and 115 for over 91.
    What am I doing wrong?

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Counting consecutive repeated numbers greater than or less than.

    You've got your criteria somewhat reversed

    Try these...ARRAY FORMULAS, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
    Max consecutive values less than 9
    Please Login or Register  to view this content.
    returns: 5

    Max consecutive values greater than or equal to 91
    Please Login or Register  to view this content.
    returns: 4

    Does that work for you?

  5. #5
    Registered User
    Join Date
    12-02-2014
    Location
    new brunswick, Canada
    MS-Off Ver
    2010
    Posts
    8

    Re: Counting consecutive repeated numbers greater than or less than.

    Thanks for your help. Can you tell me what these formulas are doing?
    =MAX(FREQUENCY(IF(A1:A58728>9,ROW(A1:A58728)),IF(A1:A58728<=9,ROW(A1:A58728))))
    =MAX(FREQUENCY(IF(A1:A58728<91,ROW(A1:A58728)),IF(A1:A58728>=91,ROW(A1:A58728))))

  6. #6
    Registered User
    Join Date
    09-17-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Counting consecutive repeated numbers greater than or less than.

    Hey Ron, I tried the formula with some adjustment to account for my scenario:

    {=MAX(FREQUENCY(IF((L5:T5>=-1.5)*(L5:T5<=1.5),ROW(L5:T5)),IF((L5:T5>=-1.5)*(L5:T5<=1.5)=0,ROW(L5:T5))))}

    I'm looking to have counted the consecutive occurrences of values >=-1.5 AND <=1.5 in L5:T5

    The range is as follows:

    0.4 -2.4 -1.6 -1.0 -1.3 -0.2 -0.1 -0.1 0.0

    The array formula returns 7 whereas the consecutive occurrences are from -1.0 -1.3 -0.2 -0.1 -0.1 0.0 so expecting a result of 5

+ 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. Identify consecutive numbers greater than zero
    By crodonilson99 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2014, 06:13 AM
  2. re: counting consecutive numbers
    By bismo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-10-2013, 04:55 PM
  3. counting only consecutive numbers over 11
    By sgk18 in forum Excel General
    Replies: 5
    Last Post: 01-30-2012, 05:16 AM
  4. Replies: 2
    Last Post: 11-12-2009, 02:57 PM
  5. counting consecutive repeated values in a column along with the range
    By g s in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-22-2005, 04:05 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