# Thread: Lottery Number Picker

1. ## 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?

2. I know this is no fun, but you could just buy a random pick. Same odds.

cheers, Paul

3. Originally Posted by Lou Sander
NumberPicker works fine as far as it goes...
You are saying, obliquely, that it is relevant only to the United States, perhaps?

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

5. Maybe you should be reading Nate Silver and Ben Goldacre about picking lottery numbers....

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

7. 1111111, 1111112, 1111113, etc... Same odds as randomly generated numbers.

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

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

10. Originally Posted by Lou Sander
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.

11. 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?

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

15. it's hard to imagine that that will ever change
Fateful words, as any programmer knows !

16. Fateful words, as any programmer knows !
I stand corrected!

Page 1 of 3 123 Last

#### Posting Permissions

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