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]

Here's the definitive answer on probability. After running through all 85,900,584 combinations., there were

4+ runs: 610,170 = 0.710321%
5+ runs: 40,678 = 0.047355%
6+ runs: 1,849 = 0.002152%
7 runs: 43 = 0.000050%

So my flawless math was flawed, assuming the code that computed the above is flawless. (The count of 7-runs is certainly correct)

 

[barry houdini]

I think your maths is spot on shg, as you said in your original post 0.76% is the probability of drawing a 4-ball or longer

sum those percentages for 4-ball to 7-ball (I assume they are for specifically 4, 5, 6 etc, do you mean to have +?)and you get the 0.7599% figure (approximately)

 

[shg]

I know you're not interested in the code, Barry, but I thought I had it set to calculate the "or longer" part (e.g., when it found a 6-run, it also bumped the 4-run and 5-run histogram). I'll look at it again.

Yoiur observation is exactly correct, I'm just missing what I'm missing.

 

[xenou]

My monte carlo is topping out at around .70%-.71% rather than the expected .76% (over several million trials). Curious. I'll try to make it presentable and put it out for review tomorrow. The way I wrote my function it doesn't remove "picked" balls when picking for the 2nd-7th digit, but I don't think that should have an effect - the remaining numbers all still have an equal chance of being drawn and if necessary I just "pick again".

 

[xenou]

I think the reason why we get less than 46 * Combin(45,3) / Combin(49,7) is that there is a little bit of double counting this way. Consider this result of 18,19,20,21,22,23,25. It would be counted as part of the 20-23 run and the 19-22 run if we count 14190 combinations for each of those runs. Edit: actually, counted in the 18-21 run too... !

 

[shg]

That's why I only considered runs of 4 or more -- there can only be one such run in a trial.

 

[xenou]

Note:
Here's my monte carlo simulation.
<a href="http://northernocean.net/etc/mrexcel/20120919_Simulation.zip">[Simulation]</a>
sha256sum: da1ca0b35cd0c4d3158e94df9f26bf551caf3f53d0a10793640778974acbc640

After 10,000,000 trials (twice) I get .7066% successes of 4+ runs - slightly less than the theoretical. Wonder if there's some slight inaccuracy in the simulation - something in the rounding to ints? other? No jackpots (yet).

 

[barry houdini]

OK, I was wrong then wasn't I? (thanks for not saying so). I missed the double-counting part, thanks xenou.

4+ runs: 610,170 = 0.710321%
5+ runs: 40,678 = 0.047355%
6+ runs: 1,849 = 0.002152%
7 runs: 43 = 0.000050%

Given these are the definitive figures, then the number of 4 runs only would be the same as the 4+ figure minus the 5+ figure i.e. 610,170 - 40,678 = 569,492

You could calculate that using a method similar to shg's, for 44 of the 46 possible runs of 4 two other numbers would be excluded (one either side) but clearly for 1,2,3,4 only 5 would be excluded and for 46, 47, 48 and 49 only 45 would need to be excluded, hence

2*COMBIN(44,3)+44*COMBIN(43,3) = 569,492

 

[shg]

Go, Barry!

Assuming my "definitive" answers are correct, I still can't reconcile them with the math in post#18. Can anyone straighten me out?

 

[shg]

After 10,000,000 trials (twice) I get .7066% successes of 4+ runs - slightly less than the theoretical.

That's very annoying -- it's consistently low.

=binomdist(10000000*0.7066%, 10000000, 0.7103%) returns 8%.

I realize that could happen (with 8% probability ) but all the runs have been low.

We're missing something here.