Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: count 1 for 15 or more consecutive 1s

  1. #1
    New Member
    Join Date
    Oct 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default count 1 for 15 or more consecutive 1s

    Guys,

    I have a column consisting of 1s and 0s/spaces. I want to count 1 for every 15 or more consecutive 1s. The result should be the number of times of consecutive fifteen or more 1s appearing on the list. What would the function be?

    Please help me on this. TY.

    Regards,
    D4ND4N

  2. #2
    Board Regular Reggie74's Avatar
    Join Date
    Sep 2014
    Location
    South Africa
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: count 1 for 15 or more consecutive 1s

    Try this ...

    Code:
    'this subroutine counts the no. of consecutive 1's appearing in a column range
    Sub countONES()
        
        Dim lastrow As Long         ' denotes the last NON-EMPTY cell in the column range
        Dim count  As Long          ' increments each time upon encountering a 1
        Dim counter As Long         ' increments each time upon encountering 15 consecutive 1's or more
        Dim startcell As String     ' used when extracting the column name in the messagebox
        Dim pos1 As Integer         ' position of the "$" sign in the cell address -> startcell
        Dim pos2 As Integer         ' position of the 2nd "$" sign in the cell address -> startcell
        
        'intialise counter
        counter = 0
        counter = 0
        
        'find the last NON-EMPTY cell in the column
        lastrow = Range("A65536").End(xlUp).Row
        
        'select the first cell in the column (in this case Column A - you can change this)
        Range("A1").Select
        startcell = ActiveCell.Address
        pos1 = InStr(1, startcell, "$")
        pos2 = InStrRev(startcell, "$", -1)
        
        For icount = 1 To lastrow
            If ActiveCell.Value = 1 Then
                'increment count each time when you encounter a 1
                count = count + 1
                
                If count >= 15 Then
                    'increment counter each time when you encounter 15 consecutive 1's
                    counter = counter + 1
                    
                    'reset count - why ? - need to start over again so as to keep track of the next 15 consecutive 1's
                    count = 0
                End If
                
            Else
                'reset count - why ? - need to start over again so as to keep track of the next 15 consecutive 1's
                 count = 0
            End If
                 
            'select the next cell
            ActiveCell.Offset(1, 0).Select
     
        Next icount
        
        MsgBox "The no. of 15 consecutive 1's in column " & Mid(startcell, pos1 + 1, pos2 - pos1 - 1) & " = " & counter, vbInformation, "Consecutive 1's ?"
        
        
    End Sub
    Quote Originally Posted by D4ND4N View Post
    Guys,

    I have a column consisting of 1s and 0s/spaces. I want to count 1 for every 15 or more consecutive 1s. The result should be the number of times of consecutive fifteen or more 1s appearing on the list. What would the function be?

    Please help me on this. TY.

    Regards,
    D4ND4N
    Last edited by Reggie74; Oct 1st, 2014 at 09:38 AM. Reason: Change column AU to Column A - just to check if msgbox picks up correct column AND adjusted count >= 15 .. :-)

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,930
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: count 1 for 15 or more consecutive 1s

    =COUNT(1/(FREQUENCY(($A$1:$A$100=1)*ROW($A$1:$A$100),($A$1:$A$100<>1)*ROW($A$1:$A$100))>14))
    which must be array entered using Ctrl+Shift+Enter

  4. #4
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,473
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count 1 for 15 or more consecutive 1s

    Hi.

    Can "consecutive" include blanks in between, or do the the rows with 1s have to be strictly following each other?

    For example, does:

    1/1/1/1/1/[blank]/1/1/1/1/1/1/1/1/[blank]/[blank]/1/1

    count as a "consecutive" string of 1s?

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,473
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count 1 for 15 or more consecutive 1s

    Quote Originally Posted by RoryA View Post
    =COUNT(1/(FREQUENCY(($A$1:$A$100=1)*ROW($A$1:$A$100),($A$1:$A$100<>1)*ROW($A$1:$A$100))>14))
    which must be array entered using Ctrl+Shift+Enter
    This is not the most rigorous construction.

    There's a strong probability that this will give an answer which is one higher than the correct value, since, if you don't use an IF clause, the zeroes in each of your arrays being passed to FREQUENCY will equally be considered, and so the result for that formula for a bin of 0 may well be over 14.

    Boolean FALSEs, however, are not considered by FREQUENCY.

    Regards
    Last edited by XOR LX; Oct 1st, 2014 at 10:01 AM.
    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,930
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: count 1 for 15 or more consecutive 1s

    Good point. I had actually originally written it with two IF clauses and then changed it just for the hell of it. Probably should have tested it again afterwards!

  7. #7
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,473
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count 1 for 15 or more consecutive 1s

    Quote Originally Posted by RoryA View Post
    Good point. I had actually originally written it with two IF clauses and then changed it just for the hell of it. Probably should have tested it again afterwards!
    Done it myself before! That's how I knew!
    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count 1 for 15 or more consecutive 1s

    If there are More than 15 consecutive 1's, say 17
    Should that be counted as 1 occurance, or 3 (1-15, 2-16, 3-17) ?

    This will count them as multiple
    =SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(A1:A100)-1,0,15,1),1)=15))
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,930
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: count 1 for 15 or more consecutive 1s



    Just for D4ND4N, this is how I originally had it (and should have left it!):

    =COUNT(1/(FREQUENCY(IF($A$1:$A$100=1,ROW($A$1:$A$100)),IF($A$1:$A$100<>1,ROW($A$1:$A$100)))>14))
    again, array entered with Ctrl+Shift+Enter.

  10. #10
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count 1 for 15 or more consecutive 1s

    For what it's worth, here's an updated version of my last post that will count more than 15 consecutive as 1 occurance.

    =SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(A1:A100)-1,0,15,1),1)=15),--(COUNTIF(OFFSET(A2,ROW(A1:A100)-1,0,15,1),1)<>15))

    Non Array entered, though the use of OFFSET probably cancels out the benefit of being Non Array entered...
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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