Analyze Lottery Numbers in Excel

[K_McIntosh]

Hey all ... my first post here ... I thought this would be a good place to start in order to clarify my understanding of some of the content re:

Analyze Lottery Numbers in Excel
at: Win the Lottery with Excel

I am motivated by both the immediate desire to come up with different ways of picking lottery numbers, as well as a desire to learn more about Excel (FYI - I'm still plunking along with Excel 2000 [9.0.8960 SP-3]) in the process - this seems like an excellent endeavor to do both.

Right out of the gate, from the above referenced page, I think I need clarification with:

"I downloaded data from the Ontario lottery site showing winning numbers for the past 3 months.

Using cut and paste, copy the numbers into a single column of data. Add a heading in cell A1.

"

At first read, this appears to work for one column of data at a time ... is it possible to do all six relevant (primary data) columns at once? ie. can we literally C & P all six columns worth of data into one single column of data ... I wouldn't have thought so ...

If we're only able to examine the frequency of a given integer being drawn on a column by column basis (ie. 1st # drawn, 2nd # drawn, ect.), my thought is that we are not seeing the frequency (or lack thereof) of partial or complete strings of six integers, as drawn historically. Or the order in which they occur, if and when they do. Apologies - it seems to me I had this latter thought expressed more clearly yesterday when I first composed this thread, however, that portion was lost due being auto-logged out and is just not coming to me as easily today, for whatever reason.

When implementing the =RANDBETWEEN function (as '=RANDBETWEEN(1,49)'), is it possible to configure the formula to select six unique integers at a time? I tried dragging the formula from one cell across six columns and found that it was occasionally possible for one (or more, although I have yet to see this) of the six integers to be duplicates.

Thanks for any help offered ... K_McIntosh

 

[shg]

What is the world's largest tax on innumeracy?

Lotteries.

 

[mirabeau]

Lotteries.

Hmm ...

I would have thought perhaps larger would be political ineptitude, with national "leaders" persistent economic bungling, failure to balance "their" (actually other people's) books, so often indulging in grandiose overblown, expensive projects (interesting phenomena at work there), stuffing their Swiss bank accounts even fatter with other people's funds ...

Wall St.? Insurance? Friedman-Savage and subsequent views?

There's a pile of literature on why people spend on lotteries or gambling generally, and not all of it has to do with innumeracy or otherwise.

Maybe a "numerate" person wishing to invest their hard-earned funds should do so in something like Bernouilli's St Petersburg game, where the expected financial reward by the numbers is .. er .. extremely high? Or maybe the good old sock under the bed is better after all.

Or maybe best of all to follow the likes of Gorgias of Leontini, who, after dropping gems such as "nothing exists, if it exists it cannot be known, if it is known it cannot be communicated", reputedly later gave up philosophy in favor of the booze and the women and died in Sicily aged about 105.

(Funny how much more interesting is this stuff than writing VBA codes)

 

[xenou]

Hmmm.

There are combin(49, 7) = 85,900,584 possible draws.

There are 46 sequences of four ascending digits (1-2-3-4 through 46-47-48-49), and there are combin(45,3) = 14,190 ways to choose the remaining 3, so I think the chances of drawing a 4-ball (or longer) straight should be

46*14190/85900584 ~ 0.76%

8.9% for a 3+ ball straight

85.7% for a 2+ ball straight

Great - thanks. So with about a .0076 chance I think we see four numbers in a row once every 2 or 3 years (if there are 52 drawings a year).

 

[barry houdini]

......reputedly later gave up philosophy in favor of the booze and the women and died in Sicily aged about 105.

Sounds like my retirement plan

 

[xenou]

Maybe a "numerate" person wishing to invest their hard-earned funds should do so in something like Bernouilli's St Petersburg game, where the expected financial reward by the numbers is .. er .. extremely high? Or maybe the good old sock under the bed is better after all.

Note that the sock under the bed does at least provide about $40,000-45,000 at retirement. Though it would be better to put it in a CD to at least keep up with inflation. I assume someone who's "lottery habit" is material - not just $1 a week but $20 a week.

It's hard to speak to the problems that society in general has brought upon itself through bad politics and other ills (there are too many!) - but it's no doubt a vast "tax" on us all as well.

@shg, using my random lottery number function, I ran a monte carlo simulation on 3000 years of lottery results (52 draws per year) and came up with an average .0073% - very close to expected value!

Edit:
@BH

 

[shg]

@shg, using my random lottery number function, I ran a monte carlo simulation on 3000 years of lottery results (52 draws per year) and came up with an average .0073% - very close to expected value!

Hmmm -- that gives a niggling doubt of my number or your trial, xenou.

3000 * 52 * 0.73% = 1139 successes

=BINOMDIST(1139, 3000*52, 0.7599%, TRUE) returns 8.5%, which is certainly possible, but ... run it a little longer?

@Barry: Given 7 numbers in ascending order, what formula tells you the LONGEST straight?

 

[xenou]

I'll try a longer run while I'm out to lunch A few million trials would take about 10 minutes I guess.

 

[xenou]

Okay, with more trials I get .7529% (40,000 years of weekly drawings). It may be there is some way that I can still optimize the trial - I'll give it a think.

 

[barry houdini]

@Barry: Given 7 numbers in ascending order, what formula tells you the LONGEST straight?

Please give a representative example with expected results

 

[shg]

1-2-3-7-8-9-10 returns 4