Lexicographic Index Numbers / Combination Sequence Numbers (CSN's) for a Lotto.

[PAB]

Hi Serge,

Yes it works, thank you very much.
So what need to be change is to bring down the formulas from U:AD?

Yes sort of, but you will notice that is a new part that I added because your original SpreadSheet didn't have it, and it needed it.
ALSO, I had to change some of your formulas because they were incorrect.
If you compare my WorkSheet formulas with your WorkSheet formuals you will understand what was wrong. There needed to be a subtraction in some of the formulas to correct the C(n, k) upper limits parameters for the possible combinations for each possible scenario of the numbers from 1 to 5.
I hope this helps!

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.

 

[serge]

Yes, I saw all the change you made on the file, this is an incredible work, I really do appreciate all the time and effort you did to help me, Thank you.

Regards,
Serge.

 

[PAB]

You're welcome Serge .

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.

 

[serge]

Hi PAB,

I'm having a small problem ! can you please download this file and look a it please, Thank you.

Regards,
Serge.

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

 

[PAB]

Please ignore, for some reason it posted before I finished it!

 

[PAB]

Hi Serge,

I'm having a small problem! Can you please download this file and look a it please, Thank you.

I have just had a quick look at the file and here are my findings.

What you can do is to put this formula =IF(J8=0,"",J8) in Cell H8 and copy down.

Then, put this formula =VLOOKUP(J1,H8:I1007,2) in Cell I1.

For some reason I think that the file is still giving the wrong results but I will need to investigate further. What I intend to do is to create the file without the columns in AO onwards.
I will run ALL 575,757 combinations against the TRUE Lexicographic numbers and the new SpreadSheet that I am going to create and see what the results are.
I will post back later as I am just going out now to have a couple of beers

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.

 

[serge]

OK, no problem I'll wait no rush you are the only one who can fix it anyway.

Question : Do you have a macro that will run ALL lexico number with their set combinations for : 5/59 5/56 5/50 5/39 ? to be able to check if the Leco is accurate with the set combination for future correction ?

That would be great but if you don't no problem.

Regards,
Serge.

 

[PAB]

Hi Serge,

Question: Do you have a Macro that will run ALL Lexicographic numbers with their set combinations for: 5/59, 5/56, 5/50, 5/39? to be able to check if the Lexicographic is accurate with the set combination for future correction?

That would be great but if you don't no problem.

Yes I have a Macro that can be adapted for ALL the scenarios you have mentioned, but that is not going to help you with this.

I have just written this Function code called Lex_2_Num for you. It will give you the correct answers 100% of the time for a C(39, 5) Lotto.
Put this code in a Standard Module and use the Function name in the formulas.

Const nMin As Integer = 1
Const nMax As Integer = 39

Function Lex_2_Num(LexVal As Long)
' =======================================================================================
' Author - Algorithm ( Program ) written by PAB on the 20-09-2013.
' Objective - Convert Lexicographic number into a combination.
' Recipient - Serge.
' =======================================================================================
Dim n1 As Long, n2 As Long, n3 As Long, n4 As Long, n5 As Long
Dim n As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
If LexVal < 1 Or LexVal > WorksheetFunction.Combin(nMax, 5) Then Exit Function
For n1 = nMin To nMax - 4
For n2 = n1 + 1 To nMax - 3
For n3 = n2 + 1 To nMax - 2
For n4 = n3 + 1 To nMax - 1
For n5 = n4 + 1 To nMax
n = n + 1
If n = LexVal Then
Lex_2_Num = Array(n1, n2, n3, n4, n5)
Exit Function
End If
Next n5
Next n4
Next n3
Next n2
Next n1
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Function

I hope this helps!
Please let me know how you get on!

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.

 

[serge]

Hi PAB,

I know this macro is not be the answer for the file I sent you, and I will wait for your corrected file.

I'm not sure how to set up the macro you sent me in the excel file if you can give me some info about how to set it up, please.

Thank you.

Regards,
Serge

 

[PAB]

Hi Serge,

I know this Macro is not be the answer for the file I sent you, and I will wait for your corrected file.

I don't undestand what you are saying here?

I'm not sure how to set up the Macro you sent me in the excel file if you can give me some info about how to set it up, please.

Here is the updated WorkSheet with the Function and Formulas included.

http://www.mediafire.com/download/b8u0q0gizsw298y/Lexicographic_2_Combination_-_PAB.xlsm

I have left in the Wrong but if you now check them I think you will find they are Correct!

Please let me know if it works!

PS: Why don't you put Los Angeles in your profile so everyone can see where you come from?

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.

 

[serge]

Hi PAB,

Thank you for everything you do, I really appreciate it.

I guess I miss understand ! so the file you just sent me is working now with the macro alone no more formulas, Right ?

I have 3 more files, Power Ball, Mega Millions and Euro Millions, so what do I have to change on the macro to work for those 3 other games, I will copy and paste your macro into my other files but since the numbers of balls are different it will need to be adjusted That I can do if I know what to change ( I don't know nothing about macro ) that's why I ask.

Regards,
Serge.

 

[serge]

I'm not sure where to go to update my profile ? right now I'm in France near Paris I will go back to Los Angeles next month.That's also why I'm working on the Euro Millions game right now.

Serge.

 

[serge]

PAB,

Just as curiosity are you a member with LP ?

Regards,
Serge.

 

[PAB]

Hi Serge,

I guess I miss understand! so the file you just sent me is working now with the Macro alone no more formulas, Right?

That's correct.
It is the Formula that uses the Function that is important.

I have 3 more files, Power Ball, Mega Millions and Euro Millions, so what do I have to change on the Macro to work for those 3 other games, I will copy and paste your macro into my other files but since the numbers of balls are different it will need to be adjusted. That I can do if I know what to change ( I don't know nothing about Macro ) that's why I ask.

ALL you need to change is the 39 in the line of code...

Const nMax As Integer = 39

...to 50, 56 or 59.

In fact, in the WorkSheet, you can delete columns P:AG as they are no longer needed.

A question for you. Have you got seperate files for Power Ball, Mega Millions and Euro Millions or are you going to use the file that I sent you for ALL four of them?

If so, I will update the Macro so that all you have to input is the total numbers drawn from and the WorkSheet will update automatically according to whether it is Power Ball, Mega Millions or Euro Millions.

If not, then you just need to copy & paste the Macro into the other files and adjust the line of code as I have instructed above.
It is the Formula that is important because it uses the Function to calculate the combinations. This formula is an Array formula and needs to be entered as an Array formula accordingly.

I hope this helps!

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]

Hi Serge,

I just had a thought, you could use the cells AC1:AE2 in the formula in cell I1 instead of hard coding the n1, n2, n3, n4 & n5 values which will make it a lot easier because you will ONLY have to change the N value and the formula will automatically update!

In other words, the formula would become...

=COMBIN(AD2,AC2)-IF(AD2-4-K1>0,COMBIN(AD2-K1,AC2),0)-IF(AD2-3-L1>0,COMBIN(AD2-L1,AC2-1),0)-IF(AD2-2-M1>0,COMBIN(AD2-M1,AC2-2),0)-IF(AD2-1-N1>0,COMBIN(AD2-N1,AC2-3),0)-IF(AD2-O1>0,COMBIN(AD2-O1,AC2-4),0)

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.

 

[serge]

Hi PAB,

Yes, I have a separate file for each game, so I will copy and paste your code for each one of them and change the number of ball accordingly to the game like you mention it.

If I encounter problem I will let you know.

I'm going to bed soon it's about 3:35 am in France right now, I will continue tomorrow.

I don't know how to thank you, You've been so helpful with this matter, This really help me a lot.

THANK YOU VERY MUCH for everything you did.

Regards,
Serge.

 

[serge]

Hi PAB,

Thanks and I will keep it for the future, but I don't need it, I create this little file for you to understand better my question, all I need is the return of the lexicographic into those combination set from K8:O8 and down.

You are an amazing guy with Excel, I would love to have your talent !

Regards,
Serge

 

[serge]

Hi PAB,

Actually you are right, your last formula will be useful for calculating with another file, to double check result.

Thank you.

Regards,
Serge.

 

[PAB]

You are very welcome Serge .

I am glad you have got a working file now, although I know that you didn't particularly want to go down the VBA Sub or Function root, but to be honest, sometimes it is a lot easier than the Formula root, this exercise is such a case.
If you have any problems then please let me know.

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.

 

[serge]

Hi PAB,

Thank you for all your help, you are really a Genius in Excel.

It's not that I didn't want to go with VBA it's just that I have no knowledge what so ever about it, I know they are faster and can handle huge task.

The formulas system you created is so close to be accurate, did you look at what was the problem to get the right return ?

I've been busy today so I didn't had time to work on my files but I will start now.If anything wrong happen, i will get on the tread for help.

Regards,
Serge.