Lotto Forums
How To Win

Go Back   Lotto Forums > Lotto Forums General Discussion > Questions & Answers

Questions & Answers Got Questions? We Have Answers.

Reply
 
Thread Tools Display Modes
  #1  
Old 10-17-2010, 07:27 AM
serge's Avatar
serge serge is offline
Registered User
 
Join Date: Jul 2010
Location: Los Angeles
Posts: 88
Binary code formula

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(n)
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.
Reply With Quote
  #2  
Old 10-17-2010, 09:27 PM
CMF's Avatar
CMF CMF is offline
Registered User
 
Join Date: Dec 2003
Location: Sydney, Australia
Posts: 180
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
Reply With Quote
  #3  
Old 10-18-2010, 05:31 AM
serge's Avatar
serge serge is offline
Registered User
 
Join Date: Jul 2010
Location: Los Angeles
Posts: 88
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.
Reply With Quote
  #4  
Old 04-06-2013, 04:19 PM
PAB's Avatar
PAB PAB is offline
Registered User
 
Join Date: Apr 2003
Location: UK
Posts: 1,584
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.
Reply With Quote
  #5  
Old 04-07-2013, 05:13 AM
Frank's Avatar
Frank Frank is offline
Registered User
 
Join Date: Aug 2009
Location: UK
Posts: 323
And out of interest, why would anybody want to do this ?
Reply With Quote
  #6  
Old 04-07-2013, 07:47 AM
PAB's Avatar
PAB PAB is offline
Registered User
 
Join Date: Apr 2003
Location: UK
Posts: 1,584
Hi Frank,

Quote:
Originally Posted by 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.
Reply With Quote
  #7  
Old 04-07-2013, 09:30 AM
Frank's Avatar
Frank Frank is offline
Registered User
 
Join Date: Aug 2009
Location: UK
Posts: 323
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.
Reply With Quote
  #8  
Old 04-07-2013, 01:17 PM
PAB's Avatar
PAB PAB is offline
Registered User
 
Join Date: Apr 2003
Location: UK
Posts: 1,584
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.
Reply With Quote
  #9  
Old 04-08-2013, 04:19 PM
PAB's Avatar
PAB PAB is offline
Registered User
 
Join Date: Apr 2003
Location: UK
Posts: 1,584
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...

Quote:
Originally Posted by serge
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.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel formula help for repeat , +1 , +2 and -1 , -2. serge Questions & Answers 4 09-13-2010 02:11 PM
Lotto program request Kwc Lotto Software 72 07-02-2008 10:58 AM
Excel - VTRAC Conversion Formula - Pick 3 Winalot2007 Questions & Answers 4 05-15-2008 01:59 PM
Curious as to the "real" number of possible draws (code help) JackpotJockey Lotto Software 4 07-27-2006 04:28 AM
Gilles...Anyone...Excel skip formula grayth Questions & Answers 1 08-26-2003 10:57 PM


All times are GMT -5. The time now is 05:20 AM.

Silver Lotto System
Silver Lotto System

 

Free Lottery Secrets Report
Free Lottery Secrets Report

 

Megamillions 125x125
NEW Lottosend

 


Get Your Best Bet Days

 

Lottosend  468x60

Powered by vBulletin
Copyright © 2000 - 2014 Jelsoft Enterprises Ltd.
Copyright 1999 - 2014 LottoForums.com