lex order formula for excel


Excel Formula for Ball Set

Hi Irvin,

You are right, it is a 649 Lotto.
It would be great if the Formula could be achieved.
I look forward to see what you come up with.
Thanks for your help.

All the best


Numbers from a lexographical value

This is a nice challenge and I have not been able to resolve this by using a formula (yet).

I currently calculate numbers from a lexographical value through a macro (cycling through all possible combinations and stopping when the appropriate value is reached).

Irvin, if you post your formula, I will certainly look at it and possibly suggest improvements.


GillesD :wavey: ,

Definitely would appreciate it.

I started on it last night and I am already going :dizzy: .

Also thought about the macro idea which I will also try.....This is an area that I am still learning so everything I am doing on excel now is great...gives me a chance to challenge myself and learn something at the same time.

The way I am constructing the formula is by a weight system.

ie the first number will have a higher lex value compared to the 6th number. Then it will look at the 2nd number etc. with the last number representing whats left of the lex value.

Whether that works or not Well soon see.


Irvin :wavey:


Numbers from a lex value

The following macro does the job. It just goes through all combinations until it reaches the lex value that is entered in cell A1. It then places the numbers N1 to N6 in cells B1 to G1.

A formula might be faster and more elegant but brute force does the job here while we come up with something.

Listing for the macro LexToNumbers:

Option Explicit

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

Sub LexToNumbers()
nVal = Range("A1").Value
nLex = 0
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
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
Range("G1").Value = F
Exit Sub
End If
Next F
Next E
Next D
Next C
Next B
Next A
End Sub


Hello GillesD :wavey:

I tried out your bit of code and it aint slow. If I blink I miss it.

I have to admit though I only need to work on 40 numbers 3.8M combinations.

My formula keeps running into a big brick wall. To be expected though.

Trying to produce 6 unknown values from 1 number is driving me :dizzy: :sick:

:lol: :bawl: :confused: :lol: :bawl:


Lexicographic Order Formula for Excel

Hi GillesD and Irvin,

Nice bit of code GillesD, works like a dream.

Irvin, how are you getting on with the Excel Formula, have you had any luck yet?.

All the Best Guys.


Hi Pab,

I actually gave up on it. Kept hitting that brick wall.

But did find GillesD little programme works great.

After studying the Lex number I have decided not to use it but still keep it there. It has been put in my lotto junk sheet pile (ie hidden column) with a few others which sound good but dont give any decent stats to go by.


Irvin :wavey:


Hi Irvin,

Yes GillesD program is sweet.
I think the Lex Number is useful to know, but as you say, I cannot see any real benefit of it.
It would have been nice to have been able to come up with an Excel Formula to get the Numbers from the Lex though.
What other information have you put in your Lotto Junk Sheet.

All the Best.
PAB :wavey:


Others in the junk / hidden pile are;

Sums, Median, Average, Decades, Lex no, Positional gap (thats what I call it), positional gap avg, positional gap med, Avg freq dist, Median freq dist.

Also row / column card layout.....but I just recently started using this again.

Have been creating lotto history v2.xls working on the idea of frequency, hot, warm, cold numbers over 14,10 and 5 draws. Also DB's announcer idea, but am still getting to grips with this one.

I have got these running in v1 but looks cluttered.

V2 has less data but the data it does have I think is more relevant in the quest for picking the correct 6.




Hi Irvin,

Thanks for the Info.
It seems that we all do the same sort of thing. We find something that seems to have a good strategy behind it and is producing better than average results, and then, sods law, it goes off the boil.
It is always worth while keeping the Info though because you never know when it is going to come back in line.

All the Best.
PAB :wavey: