Lexigraphic Lottery Values

[ststern45]

My question is as follows:

1. In a lottery game 5/39 where 5 balls are drawn from a pool of 39 balls.

=combin(39,5) = 575757 Total Combinations

2. I have an Excel worksheet that has the following:

Cells A4 through E4 I enter 5 numbers 1, 2, 3, 4, 5 and in cell F4 I have the Lexigraphic value = 000001

=COMBIN(39,5)
-IF(35-A4>0,COMBIN(39-A4,5),0)
-IF(36-B4>0,COMBIN(39-B4,4),0)
-IF(37-C4>0,COMBIN(39-C4,3),0)
-IF(38-D4>0,COMBIN(39-D4,2),0)
-IF(39-E4>0,COMBIN(39-E4,1),0)

3. Is there a way to reverse this by entering a random Lexigraphic value in Cell F5 and have the 5 digit combination appear in cell range A5 through E5?

The only possible solution is to have a worksheet with all the possible combinations (575757) in 10 columns and perform a Lookup?

Any suggestions??

Thanks,

Steve

 

[Andrew Fergus]

Hi Steve

To do this I think you will need a custom VBA solution. Copy and paste the following code into a standard module in your spreadsheet (ask if you aren't sure how) :

Public Function DeriveCombo(Population As Long, Sample As Long, ComboNumber As Long) As String

Dim LoopCount As Long, PositionNum As Long, LastPosNumber As Long
Dim Remainder As Long, tempVal As Long

LastPosNumber = 0
PositionNum = 0
Remainder = WorksheetFunction.Combin(Population, Sample)
'Debug.Print Remainder; ComboNumber
Do Until Remainder = ComboNumber
    For LoopCount = LastPosNumber + 1 To Population
        tempVal = WorksheetFunction.Combin(Population - LoopCount, Sample - PositionNum)
        If CLng(Remainder - tempVal) >= ComboNumber Then
            If PositionNum = 0 Then
                DeriveCombo = LoopCount
            Else
                DeriveCombo = DeriveCombo & "," & LoopCount
            End If
            PositionNum = PositionNum + 1
            LastPosNumber = LoopCount
            Remainder = CLng(Remainder - tempVal)
            'Debug.Print PositionNum; LastPosNumber; LoopCount; tempVal; Remainder
            LoopCount = Population
        End If
    Next LoopCount
    If Remainder = ComboNumber And PositionNum = Sample - 1 Then
        DeriveCombo = DeriveCombo & "," & Population
    End If
Loop

End Function

Then in your spreadsheet, you would get the 10,000th combination using this formula:
=DeriveCombo(39,5,10000)

HTH, Andrew

P.S. It may also be wise to introduce some error checks to avoid situations where you are seeking the 1 millionth combination which doesn't exist {e.g. aircode : if combonumber > combin(population, sample) then exit function}.

 

[ststern45]

Thanks Andrew

Thank you for your reply!!

Steve

 

[Andrew Fergus]

You're welcome.

I'm kinda chuffed with the UDF (if I may say so myself). Did you realise I built this based on the formula you provided? Which incidentally I thought was pretty clever, because I hadn't seen combin used that way to give the combination number. So I reverse engineered your formula into a UDF and it seems to work ok with other values for the Population and Sample.

But I want to reiterate the need to introduce some error checks - due to the loop that keeps going until Remainder = ComboNumber. If this condition is never met then the code will get stuck in an infinite loop (if this happens press the Ctrl+Break keys to break the loop).

As I mentioned in my late edit, you might want to introduce a check to ensure the combo number actually exists (i.e. >0 and less than the number of combinations, also the 'Population' and 'Sample' values are positive and that 'Sample' < 'Population').

I had to introduce the CLng functions which slow down the code, but they were necessary given floating point errors meant the outer loop never terminated - that is just an example of an unintended situation where the loop doesn't terminate.

So a 2nd safety check might be to introduce a 2nd counter that counts the number of times the outer loop runs. In theory this should not run more than the number entered as 'Sample'. So you could add a counter to the outer loop and if it exceeds the value in 'Sample' by a significant amount (e.g. Sample + 100) then force the code out of the loop because something has gone wrong. You will notice I commented out two lines - these were used for debugging. If something goes wrong then you can remove the comment indicators and trace the problem using the immediate window in the VBE screen.

I haven't fully tested the UDF so you may want to check it provides the correct result at the boundaries or for unusual combinations of population vs sample values.

Good luck!
Andrew

 

[ststern45]

Code from another site

Andrew,

Thanks again!!

This is from another Forum

Based on a Lottery Game that draws 5 numbers from a pool of 39 balls.

You would have to change the code below if it where a 6/49 game.

Interesting to see how to code a Powerball Type Game. 5/55 and 1/42.

===============================================

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

=============================================

 

[Andrew Fergus]

Hi Steve

I hadn't considered a brute force approach - I suppose I was seeking something a little more elegant but yes that approach will also work. That approach has hard-coded the loop values although you could set the '39 to 35' part using a passed through variable. Given the number of balls you want to draw could be variable, then the downside with the above approach is having to modify the number of loops every time you want to look at a different scenario - I suppose that was the elegant part of my approach and the fact it calculated the ball numbers based on the combination value, rather than trying every possible combination.

My code could be adapted to accomodate a powerball by having a 4th pass-through 'powerball' field (but you would set it as 'Optional'). If this was blank then the powerball would be ignored. I presume the number of combinations would be multipled by the maximum powerball number? If so, you would pass that value through in the 4th position. To incorporate a powerball, for the last ball you would loop through the number of possible outcomes for a powerball (i.e. the maximum powerball number of 42) instead of 'Population - LoopCount'. This is assuming there is only one powerball.....

Andrew