Find the Index rank lexicographic order lotto vba

[Hoareau]

Hello


how to find the Index rank lexicographic order old pullings of the lotto data in B C D E F G

thank you

 

[GillesD]

Lexographic value

In Excel, the formula

=COMBIN(49,6)-SI(44-C2>0,COMBIN(49-C2,6),0)-SI(45-D2>0,COMBIN(49-D2,5),0)-SI(46-E2>0,COMBIN(49-E2,4),0)-SI(47-F2>0,COMBIN(49-F2,3),0)-SI(48-G2>0,COMBIN(49-G2,2),0)-SI(49-H2>0,COMBIN(49-H2,1),0)

will give you the lexographic value for the 6 numbers placed in cells C2 to H2. Adjust as necessary.

In short, the lexographic value is 1 for numbers 1, 2, 3, 4, 5 and 6 (the first combination) and 13,983,816 for numbers 44, 45, 46, 47, 48 and 49 (the last one) in order.

 

[GillesD]

Small correction

Oups, I copied the formula directly from my Excel file but since I use a French version, the formula in my previous post may not work. This is the formula in English:

=COMBIN(49,6)-IF(44-C2>0,COMBIN(49-C2,6),0)-IF(45-D2>0,COMBIN(49-D2,5),0)-IF(46-E2>0,COMBIN(49-E2,4),0)-IF(47-F2>0,COMBIN(49-F2,3),0)-IF(48-G2>0,COMBIN(49-G2,2),0)-IF(49-H2>0,COMBIN(49-H2,1),0)

Not very different (IF replacing SI), but better suited for Excel in most cases.

 

[Hoareau]

Another Gilles

Thank you for the formula and the other codes posted on the forum.

Is what the algorithm of saliu is not transposable in vba