Thanks:  0
Likes:  0

# Thread: count 1 for 15 or more consecutive 1s

1. ## 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?

Regards,
D4ND4N

2. ## 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 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
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
Originally Posted by D4ND4N
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?

Regards,
D4ND4N

3. ## 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. ## 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

5. ## Re: count 1 for 15 or more consecutive 1s

Originally Posted by RoryA
=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

6. ## 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. ## Re: count 1 for 15 or more consecutive 1s

Originally Posted by RoryA
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!

8. ## 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))

9. ## 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. ## 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...