Using Excel to crack the Lottery 1

[Rockabee]

Hope the title of this thread has drawn enough interest to help with this problem.

Just for fun and something to pass the time during lunch breaks I have been wondering if Averages and MODES could be used to gain just a little bit of an advantage in predicting the lottery numbers. In the UK Lottery there has now been over 1060 draws so plenty of data to go at.

I have downloaded the draw history as a CSV file and imported it into Excel. Column A = Draw Date, Columns B to G are Balls 1 to 6 respectively, Column H = Bonus Ball, Column I = Set of Balls number, and Column J = Machine name.

The problem lies in columns B to G which are given in the order in which they were drawn, I want these to be in ascending order. I can do a row at a time by highlighting the set, then Data, Sort, Ascending, Left to right. But How can I do this for the whole set? Copying down doesn't work and I obviously don't want to go through the same routine 1061 times. Is there a quicker way.

5% to the first one that can provide an answer should I actually crack the system and win the big one.

Regards,

RB

 

[xlbo]

I think you are missing the basic premis that probability works on the basis of "it doesn't matter what has happened before - your odds stay the same"

Just because a ball has come out as 1 of the 6, 20 times in the last 30 draws has no bearing on the liklihood of it coming out the next time.....there is no way to predict things like this - it is pure luck.

In terms of your needs, the only easy way to accomplish this is with a macro.

Record yourself doing it once and then modify to loop through your selection of cells or post the recorded code back in Forum707 (VBA) here and I'm pretty sure someone will be able to help you in no time.

 

[GlennUK]

Put some new formulae in columns K through P, the first being
=LARGE($B2:$G2,6)
( assuming entering into row 2 )
and the others being
=LARGE($B2:$G2,5)
=LARGE($B2:$G2,4)
=LARGE($B2:$G2,3)
=LARGE($B2:$G2,2)
=LARGE($B2:$G2,1)
and copy down.

You can then select this entire block, copy, and do Edit/Paste Special/Values to replace the current entries in columns B through G.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Madawc]

Try figuring how many life-long losers it needs, to fund a million-pound or million-dollar winner.

Note that a life-long loser will usually have a few small wins, but not enough to cover their net losses.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Rockabee]

Thanks for the replies guys,

I am not missing the basic premise or think for one minute that a PURE LUCK system such as the lottery can be cracked. It's just like a said, a fun thing that threw up something in Excel that I didn't know how to do.

No matter what system you employ you're odds are exactly the same as the next person, 14 million to one.

You'd be thinking twice if it worked though wouldn't you?

Cheers,

RB

PS Star for Glenn for actually providing me with an answer.

 

[KenWright]

Just curious but what does sorting them buy you wrt what you are trying to achieve. Wouldn't 49 COUNTIFs ranked appropriately give you what you wanted, maybe matched up with machine etc.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Rockabee]

Curiosity, that's all Ken,

By working the Mode out of each column after they were put into ascending order and then the Average of each column I could find 2 lines of numbers that on paper "seemed" to be more successful, incidentally the Mode numbers have been more successful over the last 180 days than the Average line. I was just curious to see if any co-relation existed between the two.

As I said, a random system like this could never be "cracked" if it could then some boffin would have already done it, but I was just playing around with the numbers and came across something in Excel that I didn't know how to do, hence the post, simple as that.

Regards,

RB

 

[KenWright]

OK, cheers.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mintjulep]

Ball bubblers are pseudo-random as the weight of the balls can influence the results.

I'm not sure of the hamster wheel type.

A very interesting book about a group of students building a predictor for roulette in the late 70's or early 80's "The Eudaemonic Pie"

http://www.amazon.com/gp/product/0595142362/104-0012924-8298360?v=glance&n=283155

They found out that roulette wheels are very non-random.