#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Binary Numbers

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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Binary Numbers

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Binary Numbers

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




  #5   Report Post  
Posted to microsoft.public.excel.misc
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








  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 119
Default Binary Numbers

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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Binary Numbers

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



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 119
Default Binary Numbers

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

  #9   Report Post  
Posted to microsoft.public.excel.misc
Reg Reg is offline
external usenet poster
 
Posts: 48
Default Binary Numbers

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

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 806
Default Binary Numbers

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",""))
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
decimal to binary boardmaker Excel Discussion (Misc queries) 4 June 13th 06 09:00 PM
Solver returns non binary answer in binary constrained cells Navy Student Excel Worksheet Functions 6 September 1st 05 03:11 PM
large binary numbers Himu Excel Worksheet Functions 4 July 27th 05 02:53 AM
How can I convert binary numbers to decimal when they are larger . Nebulae Excel Worksheet Functions 1 April 14th 05 05:50 PM
Binary Numbers longer than 10 characters Andibevan Excel Worksheet Functions 2 April 6th 05 10:08 PM


All times are GMT +1. The time now is 10:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"