Thread: Binary Numbers
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Bernie Deitrick Bernie Deitrick is offline
external usenet poster
 
Posts: 5,441
Default Binary Numbers

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


"Sanford Lefkowitz" wrote in message
...
clever technique

Thanks!!


"Bernie Deitrick" wrote:

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


"Sanford Lefkowitz" wrote in message
...
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