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

[PAB]

Hi Serge,

You seem to be a guy that doesn't let go until you find the solution, that show the character of a winner

Yes you are right, I always endeavour to find an acceptable solution to whatever I am doing, I always have and always will.

In order to adapt the Lexicographic Index Number to Combination file please follow the steps below.

(1) In cell J2 change the number from 39 to 56. This will automatically update the C(n,k) combinations to 3,819,816.

(2) The way I set the file up there is NO number 39 to change (other than the one above). All the calculations are done within the formulas themselves.

(3) Now this is the CRUCIAL bit. If you do this correctly ALL the formulas will automatically adjust themselves.
Highlight cells M39:R55, RIGHT click the mouse and press INSERT, select SHIFT CELLS DOWN and press OK.

(4) Select cells M38:R38 and drag down to cells M56:R56.

Thats it!
I hope this is what you want.
Have FUN!

Regards,
PAB


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

 

[serge]

Thanks for the reply.

Regards,
Serge.

 

[PAB]

No problem, I hope it is what you were after?

Regards,
PAB


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

 

[serge]

Hi PAB,

Any luck yet with those 2 formulas ?

Regards,
Serge.

 

[Frank]

Regarding a request to look at the formulas, I have looked and found some issues. My solution works by making the calculations in stages rather than buiding up massive unmanageable formulas. I will be replying to PAB via e-mail in due course.

 

[Frank]

Having taken another look with fresh eyes this morning, there is very little requires changing in order to make PAB's sheet work properly. Its not the long formulas, its something far simpler.

 

[PAB]

To Serge and all members and guests who are following this thread.

Before I list the correct updated formulas however, I would like to say a VERY BIG THANK YOU to FRANK who was kind enough to put his time and effort into looking through my formulas and finding what was wrong.

Here are the AMENDED formulas for the LOOKUP table:

(08) In cell M1 enter the formula:

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

(09) Copy this formula down to cell M39.
(10) In cell N1 enter the formula:

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

(11) Copy this formula down to cell N39.
(12) In cell O1 enter the formula:

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

(13) Copy this formula down to cell O39.
(14) In cell P1 enter the formula:

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

(15) Copy this formula down to cell P39.
(16) In cell Q1 enter the formula:

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

(17) Copy this formula down to cell Q39.

Here are the formulas for the Lexicographic Index Number to Combination Formulas again:

(18) In cell A1 enter the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1,$M$1:$R$56,6))

(19) In cell B1 enter the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,5))

(20) In cell C1 enter the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,4))

(21) In cell D1 enter the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1),$P$1:$R$56,3))

(22) In cell E1 enter the formula:

=IF(G1=0,"",$J$2-($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1)-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1))-VLOOKUP($K$2-G1-(VLOOKUP($K$2-G1,$M$1:$R$56,1)),$N$1:$R$56,1),$O$1:$R$56,1),$P$1:$R$56,1)))

Notes:

The formulas are setup in such a way that you can copy them DOWN columns A:E if you want to test several or numerous Lexicographic Index Numbers. You CANNOT copy these formulas across the columns, they are not built in that way.

Regards,
PAB


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

 

[serge]

Hi PAB and Frank,

I tried the new formulas and it works like a charm.

Thank you very much to both of you for your hard work, I really appreciate it.

Regards,
Serge.

 

[PAB]

CORRECTION, TERMINOLOGY CONFUSION!

The file created here is for the CSN (Combination Sequence Number) and NOT the LSN (Lexicographic Sequence Number).
The calculation of the CSN and the calculation of the LSN are calculated in two very different ways.
The parameters C(n, k) where n is the total numbers drawn from, and k is the total numbers drawn are valid for BOTH CSN and LSN.

Regards,
PAB


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

 

[serge]

Hi PAB,

I've been using your formulas here but I'm encounter a small problem when I type this lexi number 253654 in cell G1 for a game of 5/50 I get this set 2 - 5 - 21 - 39 - 39 ? and also for lexi number 253741, I get a wrong return could you please look at it and maybe you'll find the error, Thank you.

Here is the Excel file already set.
https://app.boxcn.net/s/d0u0ikpbraelvsv3j0sz

 

[serge]

What the formula need to do for the second, third, forth and fifth formulas is to jump 1 number over when it come to the same number, easy to say, but this way it would be perfect no duplicate number in the same set.

 

[PAB]

Hi Serge,

Give this a try and please let me know if it's OK...

http://www.mediafire.com/download/dnkd98v437ozw0p/PAB_-_Revised_Lexi_Formlas_-_Serge.xlsx

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,

You did it flawless, your formulas work perfectly fine for any game, you are a GENIUS.

Thank you, I really appreciate your work.

Regards,
Serge.

 

[serge]

PAB,

I really like what you did, thank you again.

I tried to change the formulas to work when I scrolled them down but it wouldn't do the job ?

I'm not sure now what to change in it ?

In column G I have more Lexicographic numbers as I scroll them down, so I need the combination set to follow, could you please tell me what need to be change in the formulas to work, please.

Thank you.

 

[PAB]

Hi Serge,

In column G I have more Lexicographic numbers as I scroll them down, so I need the combination set to follow, could you please tell me what I need to be change for the formulas to work, please.

What will the maximum number of Lexicographic numbers to be used?

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]

for a 5/50 game so 2118760.

But could you explain what need to be change because I have 2 other games that I will need to adjust, Thank you.

 

[PAB]

Hi Serge,

For a 5/50 game so 2,118,760.
But could you explain what need to be change because I have 2 other games that I will need to adjust, Thank you.

Sorry, I am not following you here.
I thought you wanted the SpreadSheet to calculate the combination from a Lexicographic number, which is what I have done for you!!!
Are you saying that you want ALL 2,118,760 combinations and their respective Lexicographic numbers???

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]

No, sorry I miss understand the question.

around 1000

 

[PAB]

Hi Serge,

No, sorry I miss understand the question, around 1,000.

Give this a try and please let me know if it is OK!

http://www.mediafire.com/download/e5lv0eh42i51uaa/PAB_-_Revised_Lexi_Formlas_v2.0_-_Serge.xlsx

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 it works, thank you very much.

So what need to be change is to bring down the formulas from U:AD ?

Thanks again.