Frank I need your help from an old post.

S

serge

Member
Hi Frank,

I haven't been here for years, but starting to reuse an old excel file.

But I wanted to ask you if you could look at this excel file that you help me correcting some time ago, because there is an error somewhere, but I can't fix it.

When I type the Lexico # : 145056 for the CA Fantasy5 (5/39 ), I end up with this 5 numbers combination : 13 17 19 28 28 which is wrong,

and should be : 13 17 19 27 39

Or if you have an formula that can return the 5 numbers combination from a Lexicographic number ? for those games like :

CA Fantasy5 5/39
Mega Millions 5/75
Power Balls 5/59

If you do, that would be great, Thank you for your time, here the link for this excel file.

https://app.boxcn.net/s/vvj4m9namhpagry6ss8ck03512n6ycg2

Thank you.
Serge.
 
Frank

Frank

Member
Hi Serge,

Firstly when I enter 145056 into my converter, set for 5/39 I get 3,4,18,33,35. I have reverse tested that combination by an independent method and it returns 145056, so I think I'm right.
Having looked at your sheet, I can see it is based on the factorisation method I designed many years ago and posted in this forum. However, I was careful to split the calculations into several columns to aid checking and deugging and to help explain how it worked.
In your version someone has combined all the elements of the calculation into one HUMONGOUS complicated formula, making it virtually impossible to debug.
I can't deal with the eyestrain and confusion of trying to debug those formulas, so I recommend you use my original demo sheet which I have amended to deal with any 5 ball lottery with up to 75 balls.

https://www.mediafire.com/?u7p7uz7mydrw6ob

The only time you need to visit the "Demo and set size" page is to set the number of balls in the lottery, up to 75. This setting is transferred to the other pages. You wouldn't normally use the demo page on a regular basis, its only there to try and explain the method for converting back from a Lex value to ball numbers. It has conversion in both directions, so you can prove any Lex value calculated, by back checking using the reverse converter.

The two other pages are dedicated to convert "Numbers from Lex" or the other way "Enter Lex and get numbers". Formulas are copied down to multiple rows so multiple checks can be made. Some calculation columns are hidden on these pages.

On the "numbers from Lex" page there is also a macro generated reverse decoder. This can find a set of 5 numbers whose Lex you enter into cell 1A. You need to click a button to run the macro and get the balls. It is provided as an item of interest and as a checker against the formula based coversions. The sheets are protected to avoid accidental overwriting of formulas, but not password protected. Remove protection at your own risk. This should be compatible with Excel from 2007 onwards. I can make it compatible with earlier versions, but there would be minor conditional formatting issues.

I hope this is of help with your

CA Fantasy5 5/39
Mega Millions 5/75
Power Balls 5/59

Lotteries,

Good luck,
Frank
 
bloubul

bloubul

Member
Frank,
Another MASTER piece from you, thank you.

How ever I have a problem on Sheet "Numbers from lex" column A1. If I enter the lex nrs in a macro runs and cleared B1:F1 and than the whole sheet hangs and than you are dead in the water. Will you please look at it.

BlouBul :cool:
 
Frank

Frank

Member
Hi Bloubul. Its working perfectly for me. This extra piece of VBA was only included to make sure that when a new Lex value was entered into cell A1, that the "old" result still showing in cells "B1:F1" was cleared ready for you to click the button. Its possible that with some versions it can create a loop of code that doesnt terminate. I'll change it.

Right click on the "numbers from Lex" sheet tab. From the menu that appears, choose "view code".

You should see this code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1:F1").Select
Selection.ClearContents
Range("A1").select
Selection.Clearcontents
End If
End Sub

Delete all of that code to leave it blank below "option explicit".

Replace with

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("A1:F1").Select
Selection.ClearContents

End If
End Sub

I think the unique selection of A1 within the code might have been the problem. That line is removed.

This should stop whatever was causing the problem. If anyone else had that problem, pease let me know, as I got away with it!

If this does not cure the problem, then delete all the code on that sheet. You can manage without it.
 
Frank

Frank

Member
Ive reuploaded a corrected version to the same link.

acually, a better code to put on the page would be:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1:F1").ClearContents

Selection.ClearContents
End If
End Sub

the new upload has this code.
 
S

serge

Member
Hi frank,

Thank you for the response and the file I really appreciate it.

I also find the answer for my chart table and it work very well now, the new formula is :

=IF(R1+0< I$2,0,ROUND(COMBIN($R1,$I$2-0),0))

I'm not good with macro so that's why I wanted to update that table.
Thanks again for the help.

Serge.
 
You must log in or register to reply here.

Sidebar

Top