Lottery number generation: create a single line function.

[trillicomm]

You must install "Analysis TookPak-VBA" before doing this; check in your Excel's Tools/Add-Ins menu. Paste this long function (see below) in an Excel's worksheet (any cell) and press F9 (calculate) until you see at least 2 identical numbers. Real life's lottery does not have repeating numbers. I can disable this repetition by using cells relation but I want to use just one (1) cell. Any expert wants to challenge? If not, you can use VBA. But it still requires one (1) line of codes.

=RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)

T. Le
This message was edited by trillicomm on 2002-03-29 13:08

 

[Chris Davison]

close, but not close enough :wink:

you could still get 3 sevens or even all 6 as "14"

gotta be 6 different, from the same population (or 5 as that was the poster's version of his lottery)

o/

 

[Jack in the UK]

ive had this for about 2 year nevr had that bad results.. im upset they failed so bad your end... ermm.. like i said this is tough... a true master needs to solve this one.

 

[Chris Davison]

oh, absolutely Jack... it serves as a good method as if you do get a repetition, you just hit F9 to recalc.... new numbers in a second.

The probablitiy of it ever bringing back the number 14 six times would be 1 in (49^6)

or 13,841,287,201 to 1

(same as Celtic winning the treble next year)

which means if you recalced every second, it would take 438 years before you get all numbers the same (on average)

trouble is, it's theoretically possible, so needs adjusting in terms of a solution.

Gawd, I forgot it's work tommorrow !

 

[Mark O'Brien]

Well, since you bring Celtic into it. They've won the Treble 3 times in 114 years. So I guess that means they've got a 1 in 38 chance of winning it in any given year. However, since the League is a 2 horse race, as are the cups really, in any given year that basically gives them a 1 in 8 chance of winning the Treble next year. So not quite the 13,000,000 odd to 1. (which is about England's chance of winning the World Cup this year, without home soil advantage and dodgy linesman decisions)

To actually add something relevant to this thread. Why would you want to do this in 1 line of VBA? It would probably only be about 5 lines of VBA (possibly including the declarations). Less lines doesn't necessarily mean faster code.

 

[Jay Petrulis]

All that and no guarantee of unique results? Excellent problem, but a bit of a letdown at the end. I was expecting someone to eat some tripe and eels.

Here is some VBA code that will generalize this problem. Originally posted by Tom Ogilvy. I have added some dim statements to declare all the variables used.

The comments in the sub show the originator of the algorithm that Tom adapted.

Found at this link:
http://groups.google.com/groups?hl=en&selm=#KWlF5$RAHA.78@cppssbbsa04

---begin VBA---
Sub GenNUniqueRandom()
'
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139 [Algorithm P]
'
'
Dim list() As Long
Dim list1() As Long
Dim t As Long, i As Long, j As Long, k As Long
Dim lngTemp As Long
Dim num As Long
'Number of Unique Random Numbers you need
num = 40
'From a list of 1 to t numbers
t = 200
ReDim list(1 To t)
For i = 1 To t
list(i) = i
Next
j = t
Randomize
For i = 1 To t
k = Rnd() * j + 1
lngTemp = list(j)
list(j) = list(k)
list(k) = lngTemp
j = j - 1
Next
ReDim list1(1 To num)
For k = 1 To num
list1(k) = list(k)
Next
Range(Cells(1, 1), Cells(num, 1)).Value = _
Application.Transpose(list1)
End Sub
---end VBA---

Not a one line answer, but lightning fast.

This can be made into a UDF rather than a sub procedure.

Bye,
Jay

 

[Dave Hawley]

No way this can be done it one line, if so I'll eat some jellied eels as well

Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer)
Dim iNum As String
Dim strNum As String
Dim i As Integer

Application.Volatile
  iNum = Int((Top - Bottom + 1) * Rnd + Bottom)

   For i = 1 To Amount
       strNum = Trim(strNum & " " & iNum)
         Do Until InStr(1, strNum, iNum) = 0
           iNum = Int((Top - Bottom + 1) * Rnd + Bottom)
         Loop
   Next i
   
RandLotto= strNum

End Function

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-04-01 22:27

 

[trillicomm]

EU and US,

Can I assign a random number to a string within a cell? For ex: =$FirstPick(Randbetween(1,49)) without using VBA? This way numbers are stored in the buffer memory. Then I am able to retrieve the strings and compare two numbers. Or another way is to paste-value of the randbetween() to the surrounding cells and compare those cells. Sounds easy but it depends on Excel. I hope that makes sense.

T. Le

 

[Jack in the UK]

Hi Dave

remember we chewed this ober some 18 months ago.. MANY ways to do this... we all have our ways in VBA find i felt was best...

in function... ermmm... I still say YES it can be done, ive kinda solved and many others kinda have but in VBA its 100% thats what we need.

Still say can be done.. just bleive in Exel... how?????

 

[chessmaster]

any success?