Binary Numbers

[Sanford Lefkowitz]

This might be more of a math question than Excel.

I have an application for which I would like to know the number of "1"s in
the binary representation of a number. For example 20 in binary is 10100,
which has 2 "1"s. I do not necessarily need the binary number itself.

Also, the DEC2BIN function does not work for numbers >=512. Are there other
binary converters that do?

Thanks
Sanford

 

[Bernie Deitrick]

Sanford,

For numbers 512 and below:

=LEN(DEC2BIN(A1))-LEN(SUBSTITUTE(DEC2BIN(A1),"1",""))

For longer numbers, you could use a UDF - put this code into a standard codemodule:

Function BinCount(myVal As Long) As Integer
While myVal > 0
If myVal Mod 2 = 1 Then BinCount = BinCount + 1
myVal = (myVal - myVal Mod 2) / 2
Wend
End Function

used like

=BinCount(A1)

HTH,
Bernie
MS Excel MVP

 

[Gary''s Student]

In A1 enter:

11011011001110010101001110101011101011
as text

in B1, enter:
=LEN(A1)-LEN(SUBSTITUTE(A1,"1","")) to display 23

 

[Sanford Lefkowitz]

clever technique

Thanks!!

Sanford,

For numbers 512 and below:

=LEN(DEC2BIN(A1))-LEN(SUBSTITUTE(DEC2BIN(A1),"1",""))

For longer numbers, you could use a UDF - put this code into a standard codemodule:

Function BinCount(myVal As Long) As Integer
While myVal > 0
If myVal Mod 2 = 1 Then BinCount = BinCount + 1
myVal = (myVal - myVal Mod 2) / 2
Wend
End Function

used like

=BinCount(A1)

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Actually, it's sloppy - I should have changed this:

myVal = (myVal - myVal Mod 2) / 2

to this:

myVal = myVal \ 2

HTH,
Bernie
MS Excel MVP

 

[James Silverton]

Bernie wrote on Wed, 21 Jan 2009 09:25:11 -0500:

For numbers 512 and below:

For longer numbers, you could use a UDF - put this code into a
standard codemodule:

Function BinCount(myVal As Long) As Integer
While myVal > 0
If myVal Mod 2 = 1 Then BinCount = BinCount + 1
myVal = (myVal - myVal Mod 2) / 2
Wend
End Function

used like

=BinCount(A1)

That looks like the way to go but shouldn't BinCount be initialized to 0
?

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Bernie Deitrick]

James,

BinCount is a function name and not a variable, so it is reset to null (zero) every time the
function is called. The only time you need to initialize a function's value is when there is a
default return that is not null/zero - it can't hurt to use BinCount = 0 as the first line, but it
isn't needed.

HTH,
Bernie
MS Excel MVP

 

[James Silverton]

Bernie wrote on Wed, 21 Jan 2009 13:20:21 -0500:

BinCount is a function name and not a variable, so it is reset
to null (zero) every time the function is called. The only
time you need to initialize a function's value is when there
is a default return that is not null/zero - it can't hurt to
use BinCount = 0 as the first line, but it isn't needed.

HTH,
Bernie
MS Excel MVP

Thanks for the reply. Obviously, I am no expert on VBA but I was taught
to initialize variables in other programming languages.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Reg]

elegant!

In A1 enter:

11011011001110010101001110101011101011
as text

in B1, enter:
=LEN(A1)-LEN(SUBSTITUTE(A1,"1","")) to display 23

 

[Bernd P]

elegant!








- Zitierten Text anzeigen -

=LEN(SUBSTITUTE(A1,"0",""))