Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Lottery Number Picker

    Attached is NumberPicker.xlsm, a one-sheet workbook that lets users pick numbers for the Mega Millions and Powerball lotteries. NumberPicker picks five sets of numbers, then the user transfers them manually to a playslip.

    NumberPicker works fine as far as it goes, but it is somewhat inelegant. Two improvements would polish things up, and I'm looking for help in making them:

    • First, when presented to the user for transcribing, the five chosen numbers should be sorted from low to high. Right now, they appear in random order. (The sixth number, Mega Ball or Powerball, stands alone, and is not sorted with the others.)
    • Second, there should be no duplicates among the five chosen numbers. Right now, an Alternate number is provided; when he fills in the playslip, the user can substitute it for the first duplicate if there is one. If there are triplicates or more, the user just picks a new set of numbers.

    I have a strategy in mind for implementing the improvements, but there may be better ways to do it. My Excel and VBA skills aren't up to implementing even my own strategy, which is:

    1. Bear in mind that the Mega Millions needs five numbers from 1-74, and a separate number from 1-15. (Powerball needs five numbers from 1-69 and one from 1-26.)
    2. Get rid of the Alternate numbers, and remove the formulas from the areas under Playslip Games A through E.
    3. Make separate command buttons for Mega Millions and Powerball.
    4. Click the Mega Millions command button to recalculate and start the process below
    5. In a remote corner of the worksheet, do the following for Mega Millions. Powerball would be similar...
    6. Use RANDBETWEEN to put 5 Mega Millions numbers, plus a Mega Ball, in a range somewhere out of sight. Call it MegRandom.
    7. Copy MegRandom to the Clipboard
    8. Using Paste Values, put MegRandom into a range of cells somewhere out of sight. Call it MegValues. (The pasting will cause the workbook to recalculate, putting different numbers in MegRandom).
    9. Sort MegValues from low to high.
    10. Look for duplicates in MegValues. If you find any, go back to Step 7.
    11. Once MegValues has been sorted and there are no duplicates, copy it and paste it into the range under Playslip Game A in the main area of the sheet.
    12. Repeat the above four times, pasting into the ranges under Playslip Games B, C, D, and E.
    13. Something similar would happen when the Powerball command button is clicked, except RANDBETWEEN would use the Powerball ranges of acceptable numbers, and the pasting would be into Playslip Games under the Powerball Numbers.

    Does any of this make sense?
    Attached Files Attached Files
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    I know this is no fun, but you could just buy a random pick. Same odds.

    cheers, Paul

  3. #3
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,413
    Thanks
    33
    Thanked 195 Times in 175 Posts
    Quote Originally Posted by Lou Sander View Post
    NumberPicker works fine as far as it goes...
    You are saying, obliquely, that it is relevant only to the United States, perhaps?
    BATcher

    Time prevents everything happening all at once...

  4. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    PaulT - Yes, but some people won't buy the Quick Picks because they suspect they are rigged. Others want to pick numbers in advance, without buying tickets just to get the numbers. (That's me. I need to pick in advance 400 sets of numbers to use when needed HERE. It gets mighty tiresome trying to come up with new ones.)

    Also, when NumberPicker is in its best form, I'm planning to put a download link to it HERE.

    BATcher - Well, it's relevant anywhere they have Mega Millions and Powerball, or, mutatis mutandis, to anywhere that similar groups of numbers are used.
    Last edited by Lou Sander; 2016-01-22 at 11:46. Reason: Add another link
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #5
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,720
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Maybe you should be reading Nate Silver and Ben Goldacre about picking lottery numbers....

  6. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I'm sure they are very bright guys, but have they ever won anything in a lottery? The USS Rankin Lottery Pool has won money in over 1,400 consecutive drawings since 2007.

    But, like many lottery players, we are in it mostly for the fun.
    Last edited by Lou Sander; 2016-01-22 at 12:06. Reason: Link and record of winnings
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,160
    Thanks
    19
    Thanked 99 Times in 88 Posts
    1111111, 1111112, 1111113, etc... Same odds as randomly generated numbers.
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  8. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Of course, if all you consider is probability theory. But read this from our Rules and Procedures:

    ... while it is interesting to consider mathematical chances of winning, mathematics says nothing at all about which ticket, person, or pool might win a Jackpot or any other prize. It calculates abstract chances without any consideration of luck, natural or supernatural activities favoring a given person or ticket, the possibility of being able to bring success by visualizing it, willing it, praying for it, cheating, or any similar thing.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  9. #9
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,413
    Thanks
    33
    Thanked 195 Times in 175 Posts
    I rather like the truism that the chances of a person winning the lottery are only minimally greater if they had actually bought a ticket than if they hadn't.
    BATcher

    Time prevents everything happening all at once...

  10. #10
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,160
    Thanks
    19
    Thanked 99 Times in 88 Posts
    Quote Originally Posted by Lou Sander View Post
    Of course, if all you consider is probability theory. But read this from our Rules and Procedures:
    I don't know what "club" your rules are from, but if you enjoy coming up with this sort of thing then fine. And I'll just leave it at that.
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  11. #11
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    BATcher - I like two truisms:

    1) The guy who doesn't buy a lottery ticket has an infinitely smaller chance of winning than the guy who buys only one.

    2) Q. What's the difference between a man who buys a lottery ticket and a man who has an argument with his wife?

    A. The guy with the lottery ticket has a chance to win.

    All those things being said, is there anybody out there who can help with Excel?
    Last edited by Lou Sander; 2016-01-23 at 07:05.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  12. #12
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    I'd do the calculation in a macro as this makes it easy to check for duplicate numbers - generate the next number and compare it with all previous, if it matches, regenerate. You could set rules on the sheet that the macro uses to calculate the numbers - in case they change the numbers used.

    I'd scratch something up for you if I had Excel...

    cheers, Paul

  13. The Following User Says Thank You to Paul T For This Useful Post:

    Lou Sander (2016-01-23)

  14. #13
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Good idea! The lotteries DO change their formats from time to time. So far, the changes have all been in the ranges of numbers used for the fields of five numbers and those for the special balls.

    I guess I need four constants in the macros: Highest number used for Powerball "field", highest number used for Powerball "special ball", highest number used for Mega Millions "field", and highest number used for Mega Millions "special ball".

    The lowest number for all of them has always been 1, and it's hard to imagine that that will ever change.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  15. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    it's hard to imagine that that will ever change
    Fateful words, as any programmer knows !

  16. #15
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Fateful words, as any programmer knows !
    I stand corrected!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Page 1 of 3 123 LastLast

Posting Permissions

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