Binary Numbers

  • Thread starter Thread starter Sanford Lefkowitz
  • Start date Start date
S

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
 
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
 
In A1 enter:

11011011001110010101001110101011101011
as text

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

Thanks!!


Bernie Deitrick said:
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
Click to expand...
 
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
 
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:
Click to expand...
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
Click to expand...
used like
Click to expand...
=BinCount(A1)
Click to expand...

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
 
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
 
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.
Click to expand...
HTH,
Bernie
MS Excel MVP
Click to expand...
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
 
Back
Top