count runlengths

[John Jones]

I recently had occasion to count runlengths of 0s and 1s in a binary
decimal. The digits were in one long column, one cell each.
I couldnt think of an excel formula to do this and was feeling out of
sorts with VBA. Is there a magic method?

(In the end I eyed the 327 digits and did the count by hand.)
HTH
JJ

 

[isabelle]

hi,

=IF(A1=1,2^(ROW()-1),0)
then fill down

isabelle

Le 2014-09-05 08:06, John Jones a écrit :

 

[isabelle]

or,

Function Binary_Decimal(rng As Range)
For i = 1 To rng.Count
If rng(i) = 1 Then n = n + (2 ^ (i - 1))
Next
Binary_Decimal = n
End Function

isabelle

Le 2014-09-05 13:50, isabelle a écrit :

 

[John Jones]

or,

Function Binary_Decimal(rng As Range)
For i = 1 To rng.Count
If rng(i) = 1 Then n = n + (2 ^ (i - 1))
Next
Binary_Decimal = n
End Function

isabelle

Le 2014-09-05 13:50, isabelle a écrit :

Many thanks Isabelle. These methods do convert binary to decimal.

I probably didnt explain what I wanted to do properly.
if the cells were (in a column)
1 0 0 0 1 1 1 1 0 0 1 0 0 0 1 1 1
I wanted to compute the number of times each digit occurs,
in this example we get
1(1) 0(3) 1(4) 0(2) 1(1) 0(3) 1(3)
So it is the numbers in brackets () I wanted.

Cheers
JJ

 

[Claus Busch]

Hi John,

Am Sat, 6 Sep 2014 12:02:19 +0100 schrieb John Jones:

if the cells were (in a column)
1 0 0 0 1 1 1 1 0 0 1 0 0 0 1 1 1
I wanted to compute the number of times each digit occurs,
in this example we get
1(1) 0(3) 1(4) 0(2) 1(1) 0(3) 1(3)

put the follwing code in a standard modul and call in from the sheet
with =runlength(A1)
Function runlength(myRng As Range) As String
Dim i As Long, counter As Long

counter = 1
For i = 2 To Len(myRng)
If Mid(myRng.Text, i, 1) <> Mid(myRng.Text, i - 1, 1) Then
runlength = runlength & Mid(myRng.Text, i - 1, 1) & " (" & counter
& ") "
counter = 1
Else
counter = counter + 1
End If
Next
End Function


Regards
Claus B.

 

[isabelle]

'each component of 10001111001000111 is placed in cells A1:Q1
'with =Special_Binary1(A1:Q1)

Function Special_Binary1(rng As Range) As String
Dim n As Long
n = 1
For i = 2 To rng.Count
If rng(i) = rng(i - 1) Then n = n + 1 Else n = n & 1
Next
Special_Binary1 = n
End Function

'whether 10001111001000111 is put in a cell
'with A6 = =Special_Binary2(A6)

Function Special_Binary2(rng As Range) As String
Dim n As Long
n = 1
For i = 2 To Len(rng)
If Mid(rng, i, 1) = Mid(rng, i - 1, 1) Then n = n + 1 Else n = n & 1
Next
Special_Binary2 = n
End Function

isabelle

Le 2014-09-06 07:02, John Jones a écrit :

 

[John Jones]

On Sat, 6 Sep 2014 12:02:19 +0100, "John Jones" <[email protected]>
wrote in article <[email protected]
september.org>...

Many thanks Isabelle. These methods do convert binary to decimal.

I probably didnt explain what I wanted to do properly.
if the cells were (in a column)
1 0 0 0 1 1 1 1 0 0 1 0 0 0 1 1 1
I wanted to compute the number of times each digit occurs,
in this example we get
1(1) 0(3) 1(4) 0(2) 1(1) 0(3) 1(3)
So it is the numbers in brackets () I wanted.

Cheers
JJ

Many thanks to Clause and Isabelle
Cheers
JJ