+ Reply to Thread
Results 1 to 9 of 9

Largest and smallest sequences

  1. #1
    Registered User
    Join Date
    01-12-2009
    Location
    uk
    MS-Off Ver
    Excel 2003
    Posts
    28

    Largest and smallest sequences

    Hi. I am using excel 2007 and I need help with the following if anyone would be kind enough. I have a dynamic array that consists of stream of 1's and 0's. I want to be able to find the largest sequence of 1's and the smallest sequence of 1's. So at a point in time the array mite be 111110001101111000 and I want to be able to dertermine the longest contiguous sequence of 1's. But as I say the array is dynamic and so I need to do that after each input. All and any help appreciated. Thanks.
    Last edited by smileyc; 01-13-2009 at 10:54 AM.

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

    largest and smallest sequences

    Are the values all in one cell?
    Are they spread across a row or column?
    Can you give some more samples along with the values you want to see for those samples?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Assuming a row or column vector named Vec, the array formula:

    =MAX( FREQUENCY( IF( Vec=1, ROW(Vec) ), IF( Vec<>1, ROW(Vec) ) ) ) returns the largest.

    This ugly array formula returns the shortest:

    =MIN( IF(FREQUENCY( IF( Vec=1, ROW(Vec) ), IF( Vec<>1, ROW(Vec) ) ) >0, FREQUENCY( IF( Vec=1, ROW(Vec) ), IF( Vec<>1, ROW(Vec) ) ) ) )

    It can surely be simplified.
    Last edited by shg; 01-12-2009 at 04:14 PM.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    01-12-2009
    Location
    uk
    MS-Off Ver
    Excel 2003
    Posts
    28
    Hi and thanks for your help thus far. The data are in different sequencial cells, so that A1 has either a 1 or 0 in it, after the next input A2 has either a 1 or 0 in it and so on. So after a period of time I have a row that might have a sequence in it like this 001110011111001110 and this sequence grows with each new input. I would like to be able to dertermine after each input the longest sequence of contiquous 1's and the shortest contiguous sequence of 1's. Again, thank you for your help.

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Quote Originally Posted by smileyc View Post
    Hi and thanks for your help thus far. The data are in different sequencial cells, so that A1 has either a 1 or 0 in it, after the next input A2 has either a 1 or 0 in it and so on. So after a period of time I have a row that might have a sequence in it like this 001110011111001110 and this sequence grows with each new input. I would like to be able to dertermine after each input the longest sequence of contiquous 1's and the shortest contiguous sequence of 1's. Again, thank you for your help.
    In that case, you could still use the formulas offered above, but you would need a special cell at the end that concatenates all the cells together. The simplest would be to add a special UserDefinedFunction to do it easily.

    Paste this code into a standard macro Module. Sorry, it's a little long:
    Please Login or Register  to view this content.
    Now, at the end of all your 1 and 0 cells, pick a column and enter this formula:

    =StringConcat("",A2:K2)

    Obviously you need to adjust that range of cells to match your range. You should get an answer that is a single display of all your cells shown together. Copy that down the column if more StringConcat-ing is needed.

    Now you can run the functions SHG suggested against these cells.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  6. #6
    Registered User
    Join Date
    01-12-2009
    Location
    uk
    MS-Off Ver
    Excel 2003
    Posts
    28
    Hi again. Thanks for the macro, it works a treat but I'm still a bit stuck. For the purposes of understanding this, I have a range called vec, b2:b58 full of data. Using the macro this has been concantated into into cell AA2 using the formula as suggested string concat "" b2:b58 which results in the following :-

    001000010000100011110111010110111011010001111100000001000

    however, and unfortunately, the formula suggested above,=MAX( FREQUENCY( IF( vec=1, ROW(vec) ), IF( vec<>1, ROW(vec) ) ) ) results in a #VALUE error, and my understanding of what is happening and what needs to be done isn't sufficient to solve it. Once again thank you for your help.

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    SHG (or others) can best help at this point by seeing your book, go ahead and post up a sample showing the errors in action.

  8. #8
    Registered User
    Join Date
    01-12-2009
    Location
    uk
    MS-Off Ver
    Excel 2003
    Posts
    28
    Oh wow! It works.Thanks for the replies, the reason I was getting an error was my failure to enter shg's formula as an array formula. Anyway the first array formula is working fine not had time to test the other, but many thanks for the help and the speed of the replies.

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Great. Team effort, eh?

    If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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