# Largest and smallest sequences

1. ## 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.  Register To Reply

2. ## 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?  Register To Reply

3. 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.  Register To Reply

4. 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.  Register To Reply

5. Originally Posted by smileyc 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.  Register To Reply

6. 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.  Register To Reply

7. 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.  Register To Reply

8. 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.  Register To Reply

9. 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]  Register To Reply

##### Users Browsing this Thread

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