Binary Numbers

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


"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

In A1 enter:

11011011001110010101001110101011101011
as text

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

--
Gary''s Student - gsnu200828


"Sanford Lefkowitz" wrote:

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

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

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

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

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)

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


"James Silverton" wrote in message
...
Bernie wrote on Wed, 21 Jan 2009 09:25:11 -0500:

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)

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 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


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

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

elegant!


"Gary''s Student" wrote:

In A1 enter:

11011011001110010101001110101011101011
as text

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

--
Gary''s Student - gsnu200828


"Sanford Lefkowitz" wrote:

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

On 12 Mai, 15:01, Reg wrote:
elegant!



"Gary''s Student" wrote:
In A1 enter:

11011011001110010101001110101011101011
as text

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

--
Gary''s Student - gsnu200828

"Sanford Lefkowitz" wrote:

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- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

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