Lexicographic number.

[serge]

Hi Everyone.

I'm looking for a formula that would give me the combination of 5 numbers according of the Lexicographic number ? The game is 5/56.

I have it the other way around, I type the combination from A1:E1 and it give the Lexico number in G1.

In the example below in G4, I put the Lexicographic number and I need in A4:E4 the combinaison corresponding to that number.

Excel Workbook
	A	B	C	D	E	F	G	H
1	1	2	3	4	5		1
2	52	53	54	55	56		3819816
3
4							3819816
5
Sheet1

I already have the macro that do the job but I need a formula, Thank you.
Serge.

 

[GlennUK]

I already have the macro that do the job but I need a formula

Maybe showing your macro code will give us some clue as to what logic is to be used.

 

[serge]

Thank you for the reply.
Here is the macro.

Function LexToNums(LexVal As Long)
Dim a&, b&, c&, d&, e&, v&
If LexVal < 1 Or LexVal > 3819816 Then Exit Function
For a = 1 To 52
For b = a + 1 To 53
For c = b + 1 To 54
For d = c + 1 To 55
For e = d + 1 To 56
v = v + 1
If v = LexVal Then
LexToNums = Array(a, b, c, d, e)
Exit Function
End If
Next
Next
Next
Next
Next
End Function


Hope it can help.

Thank you, GlennUK.

 

[GlennUK]

Ok, I see what you mean now.

Combination 1,2,3,4,5 is the first combination, and combination 52,53,54,55,56 is the 3819816th combination. And you want to be able to specify the combination number ( the nth combination ) and generate the combination elements for it.

I'm sure that there must be a way to calculate that mathematically. I'll have a think about it, but if there any mathematicians out there who can help, that'd be good.

By the way, as you have a macro solution, why are you now asking for a formula solution?

 

[serge]

Because I don't know nothing about macro, but with a formula I would be able to adapt them to different pool number, this one is at 5/56 other are at 5/36 or 5/39 or 6/49 etc... and also all my work is with formulas.

Thank you Glenn for the reply.
Serge.

 

[shg]

You could replace your UDF with a formula:

<TABLE style="WIDTH: 226pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=300 x:str><COLGROUP><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 2230" span=5 width=49><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2523" width=55><TBODY><TR style="HEIGHT: 20.4pt" height=27><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; HEIGHT: 20.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 height=27 width=49>C1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=49>C2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=49>C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=49>C4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=49>C5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=55>C#</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30 height=16 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30 align=right x:num>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30 align=right x:num>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num="1" x:fmla="=SUMPRODUCT(IF(A2:E2-{1,2,3,4,5}=0, 0, COMBIN(A2:E2 - 1, {1,2,3,4,5}))) + 1" x:arrayrange="F2">1 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30 height=16 align=right x:num>52</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30 align=right x:num>53</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30 align=right x:num>54</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30 align=right x:num>55</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl30 align=right x:num>56</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 align=right x:num="3819816" x:fmla="=SUMPRODUCT(IF(A3:E3-{1,2,3,4,5}=0, 0, COMBIN(A3:E3 - 1, {1,2,3,4,5}))) + 1" x:arrayrange="F3">3,819,816 </TD></TR></TBODY></TABLE>

In C2 and down, confirmed with Ctrl+Shift+Enter,


=SUMPRODUCT(IF(A2:E2 - {1,2,3,4,5} = 0, 0, COMBIN(A2:E2 - 1, {1,2,3,4,5}))) + 1

Going the other way is harder.

 

[serge]

Wow, this formula is quite shorter than the one I presented.

Thank you, shg.

Like you said " Going the other way is harder " but I need it for my work, so I hope someone will be able to create those formulas.

Thanks again shg.
Serge.

 

[shg]

I can provide simple UDFs that go both ways, but ..

Balls are numbered 0 to N-1 (e.g., 0 to 55, not 1 to 56), and

The lexically ordered series starts with the highest number (e.g., {4,3,2,1,0}) is first.

 

[mikerickson]

No, I'm not correct.

 

[serge]

shg,

I need formulas like you did in the previous post to solve my problem.

I search all over but no success so far.

That's why I'm posting here, you guys are the masters with Excel.

Thanks.