Binary code formula

[serge]

Hi everyone,

I need some help with correcting, a macro, someone already looked at it and told me that this macro is correct but can not handle big number !!!

So what I need is for this macro be able to transfer any number from 1 to 575757 and higher if possible ? for the game 5/39.

Here it is :

Function D2B(ByVal n As Long) As String
n = Abs
D2B = ""
Do While n > 0
If n = (n \ 2) * 2 Then
D2B = "0" & D2B
Else
D2B = "1" & D2B
n = n - 1
End If
n = n / 2
Loop
End Function

I have in column " I " all my Lexicographic numbers and in column " K " the Binary code of this Lexicographic number which total of 20 digits.

I have a second question :

I need each of those Binary code number to have their digits brook down, in separated cell ( 1 digit per cell ), so starting in cell O5 and up to AH5 I have this formula but it doesn't work for the last 4 digits ??

Here is the formula : =VALUE(MID(TEXT($K5,"000000000000000000000"),CELL("col",O5)-CELL("col",$K5),1))

So if someone can help, I would really appreciate it,

Thank you all.

Serge.

 

[CMF]

Decimal to Binary - easy when you do a search on the web.

In the debug window -
? Dec2Bin(575757)
10001100100100001101

Available from this link and many others: -
http://forums.devx.com/showthread.php?t=69341

Colin Fairbrother

 

[serge]

Thank you CMF,

But I'm not a macro guy at all, I can't fix it without details help.
Thank you for your quick response, Serge.

 

[PAB]

Hi serge,

I was looking through old threads in this Forum and came across this one.
I don't know if you sorted it out but I have put some code together that will split the Binary number into the columns you requested starting at cell O5.

Sub Text_To_Col_PAB()
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
.ErrorCheckingOptions.NumberAsText = False
End With
Range("K5:K" & Range("K" & Rows.Count).End(xlUp).Row).Copy
Range("M5").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("O5"), DataType:=xlFixedWidth, _
FieldInfo:=Array( _
Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), _
Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), _
Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1)), _
TrailingMinusNumbers:=True
Range("O1").Select
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
' .ErrorCheckingOptions.NumberAsText = True
End With
End Sub

Please let me know what you think!

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[Frank]

And out of interest, why would anybody want to do this ?

 

[PAB]

Hi Frank,

And out of interest, why would anybody want to do this ?

I totally agree with you, I cannot see any statistical benefit or any other come to that for this information, but I was just scanning the archives and came across it and thought I would have a go to see if I could do it using VBA, that's all.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[Frank]

I just wondered if there was any speed advantage in terms of computational times by manipulating binary rather than normal decimal representation. There was no suggestion that exploring it to solve the originators problem wasn't productive. As usual, an impressive piece of code saves the day. Maybe Serge can tell us why its useful. However, it an old post so maybe we've moved on.

 

[PAB]

Hi Frank,

I thought I would pick this up and run with it again and try and find a NON VBA solution to calculate the Binary number.

This as it turned out was not as easy as I first thought and I have spent about three hours this afternoon researching it, BUT, I have learned quite a few things and have enjoyed it.

I scanned the Internet and found that this procedure is to do with the number of Bits and how they are structured and manipulated. Excels built in DEC2BIN function is structured to Decimal numbers which are Base 10 and Binary numbers which are Base 2. I have concluded that the LARGER the number to convert from Decimal to Binary then the more Bits are needed to achieve this.

It appears that Excel 2007's DEC2BIN function begins to fail with decimal numbers somewhere between 2^8 and 2^9 which equates to if the number is < -512 or > 511 and if this is the case then the DEC2BIN returns the #NUM! error value, which to be honest is quite useless in real terms.

I found this that describes pretty well the process of how Decimal and Binary numbers are structured:-

"Decimal notation describes numbers using the digits 1 through 10. Binary notation describes them using just two digits, 1 and 0, where each bit in a string represents a power of 2. The right-most bit represents 2 raised to the power of 0, the next right-most bit represents 2 raised to the power of 1, and each remaining digit represents double the previous one's quantity. Microsoft Excel converts from binary to decimal notation using the BIN2DEC function and from decimal to binary using the DEC2BIN function."

The thing is that Excels DEC2BIN function is limited for the reasons I have stated above and therefore to achieve the correct results the Binary string needs to be broken down.

Anyway, after looking at MANY MANY formulas, 99% of them which were of no use for the maximum value of 575,757 that serge stated, I decided to put all that new gained knowledge to use and build a new formula that will accomodate all those combinations without the need of VBA code. I managed to achieve this and then wrapped it within a Right formula to ONLY produce the MAXIMUM number of digits required for the UPPER Binary limit of 575,757.

THE FORMULA ( Cell K5 and copied down ):-

=RIGHT(DEC2BIN(INT(MOD(I5,2^27)/2^18),9)&DEC2BIN(INT(MOD(I5,2^18)/2^9),9)&DEC2BIN(MOD(I5,2^9),9),20)

IMPORTANT

You need the Analysis Toolpak Add-In installed.
This will not affect the code I wrote.

Well, as I said previously, I have not only enjoyed doing this but I have learnt a hell of a lot, although there is NO circumstances that I can see where this data can be applied to the Lotto and give any beneficial results or statistical analysis.

The formula can be adapted to accomodate all the C(49,6) total combinations.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[PAB]

Just to tidy this thread up for anyone following it and does NOT want to use a VBA solution to split the Binary number, here is an Excel formula to do it for you.

I don't know where or why serge used this one...

Here is the formula : =VALUE(MID(TEXT($K5,"000000000000000000000"),CELL( "col",O5)-CELL("col",$K5),1))

...but it doesn't work.

Here is the formula to use in cell M5 and copied across to cell AF5:-

=MID($K5,COLUMNS($K5:K5),1)*1

I think that about wraps this thread up!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.