Excel Help - GillesD - Lexigraphic Formula Question

[Winalot2007]

To all the Excel experts here.

There are a number of excellent posts on Excel and VBA here.

I have a question about Lexigraphic formulas.

There was a post here on this forum that provided a formula for generating a Lexigraphic Number based on the lottery combination:

=COMBIN(39,5)
-IF(35-$A1>0,COMBIN(39-$A1,5),0)
-IF(36-$B1>0,COMBIN(39-$B1,4),0)
-IF(37-$C1>0,COMBIN(39-$C1,3),0)
-IF(38-$D1>0,COMBIN(39-$D1,2),0)
-IF(39-$E1>0,COMBIN(39-$E1,1))

For example: Cash5/39 Game

1,2,3,4,5 = Lexigraphic # 000001

35,36,37,38,39 = Lexigraphic #575757

Is there a way you can enter the Lexigraphic number and generate the Pick 5 combination?

Thanks,

Steve

 

[Winalot2007]

I Found It!!

Thank you to the person who provided this code!!

Modified for Cash 5/39 Game.
------------------------------------------------------------------------

Option Explicit

Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer
Dim nVal As Double, nLex As Double

Sub LexToNumbers()
nVal = Range("A1").Value
nLex = 0
For A = 1 To 35
For B = A + 1 To 36
For C = B + 1 To 37
For D = C + 1 To 38
For E = D + 1 To 39

nLex = nLex + 1
If nLex = nVal Then
Range("B1").Value = A
Range("C1").Value = B
Range("D1").Value = C
Range("E1").Value = D
Range("F1").Value = E

Exit Sub
End If

Next E
Next D
Next C
Next B
Next A
End Sub

 

[PAB]

Hi Steve,

It was either GillesD or myself.
Another term for a Lexicographic Index Number is a Combination Sequence Number, better known as CSN.
I am glad you found the code useful.

All the Best.
PAB

 

[Winalot2007]

Thank You

Hi PAB,

Thanks to both for the VBA code.

It amazes me how you both come up with this code.

Thanks again!!

 

[variablinda369]

any one know the lexicography order vba code for the 6/53 lotto my excel is 2003, I would like to see the history winning list with the lexi... numbers. thanks.

 

[PAB]

Hi variablinda369,

Welcome to the Board.

Another term for a Lexicographic Index Number is a Combination Sequence Number, better known as CSN.

Are you saying that you have a list of the combinations drawn (numbers) for your 6/53 Lotto and would like to see what each combinations Lexicographic Index Number (CSN) is?

If so, I will provide you with an Excel formula to do this.

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.

 

[Winalot2007]

Lexi 653

If your set is as follows:
P1# = A1
P2# = B1
P3# = C1
P4# = D1
P5# = E1
P6# = F1

=COMBIN(53,6)-IF(48-A1>0,COMBIN(53-A1,6),0)-IF(49-B1>0,COMBIN(53-B1,5),0)-IF(50-C1>0,COMBIN(53-C1,4),0)-IF(51-D1>0,COMBIN(53-D1,3),0)-IF(52-E1>0,COMBIN(53-E1,2),0)-IF(53-F1>0,COMBIN(53-F1,1))

any one know the lexicography order vba code for the 6/53 lotto my excel is 2003, I would like to see the history winning list with the lexi... numbers. thanks.

 

[PAB]

You beat me to it Winalot2007, I was just about to post a follow up.

The following method will accommodate ALL 6 from "n" Lotto's without the need to change the maximum number drawn in the Excel formula itself.

Let's assume that you have your drawn results in cells O16:T16 in numerical order and NOT as actually drawn.
Now we will setup a cell that holds the number the Lotto is drawn from, in your case this will be 53, but this can be the maximum number drawn in any Lotto.
We will use cell W12 for this, therefore in cell W12 enter the number 53.
In cell W16 enter the formula:-

=COMBIN(W$12,6)-IF(W$12-5-O16>0,COMBIN(W$12-O16,6),0)-IF(W$12-4-P16>0,COMBIN(W$12-P16,5),0)-IF(W$12-3-Q16>0,COMBIN(W$12-Q16,4),0)-IF(W$12-2-R16>0,COMBIN(W$12-R16,3),0)-IF(W$12-1-S16>0,COMBIN(W$12-S16,2),0)-IF(W$12-T16>0,COMBIN(W$12-T16,1),0)

and copy down as far as needed.
I have used this Excel formula from draw one for my particular 6/49 Lotto without any problems.
Let me know how you get on.

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.

 

[PAB]

For anyone who is interested I have put something together for Lexicographic Index Numbers / Combination Sequence Numbers (CSN's) in a Lotto in a new thread.

Here is the link:

http://www.lottoforums.com/lottery/questions-answers/12740-lexicographic-index-numbers-combination-sequence.html#post122848

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.