Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 29 of 29

Thread: Lottery number generation: create a single line function.

  1. #21
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    close, but not close enough

    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/

  2. #22
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #23
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 !

  4. #24
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  5. #25
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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=e...%40cppssbbsa04

    ---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



  6. #26
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Code:
    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 ]

  7. #27
    Board Regular
    Join Date
    Feb 2002
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #28
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?????
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  9. #29
    New Member
    Join Date
    Sep 2009
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lottery number generation: create a single line function.

    any success?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •