Lotto Tips & Strategies Methods and Systems for Winning the Jackpot. 
03292008, 04:47 PM

Registered User


Join Date: Mar 2008
Posts: 3


Positional Analysis in Excel?
I am new to this forum so first let me say Hi.
I am wanting to make a chart in excel showing the freq of the numbers picked in each position in pick3 and pick4 lotteries. I have the past drawings in a spreadsheet with position1  position2  position3 across the top. How do I do the formula to get the positional analysis and then put it in a chart? Thanks for any help you can give.
Satch

03292008, 11:43 PM


Registered User


Join Date: Oct 1999
Location: Montréal
Posts: 715


Graph of numbers per position
Let's say your data of past drawings is in columns A, B and C from row 1 to 90, to get the distribution of numbers 0 to 9 per position #1, #2 and #3, do the following:
 in a range like F4 to F13, enter the numbers 0 to 9 sequentially
 select the range G4:G13 and enter the formula =FREQUENCY(A1:A90;F4:F13) and press ShiftCtrlEnter to get an array formula (it will be in brackets)
 repeat in range H4:H13 but change the first range in the formula from A1:A90 to B1:B90 and also in range I4:I13 with range A1:A90 changed to C1:C90
 adjust as necessary depending where is your data or to have a fourth position.
Then make a graph using the range F4:I13 as data to view the results. If your database is fairly large, all numbers should be about equally distributed across all positions.
Using this technique with a 6/49 lottery gives a very nice graph, since all numbers cannot happen with the same frequency at all positions (if the data is in increasing order).

03302008, 12:18 AM


Registered User


Join Date: Jul 2006
Location: Encinitas, CA U$A
Posts: 4,787


Hello Satch, I don't make an actual spreadsheet chart, instead I just make a 3x10 cell matrix to track digit distribution by position for each ballslot at a specific interval.
Assuming A1:C28 contains the last 4weeks of draws, the first column of the digit matrix I label DIGIT, and type ZERO, ONE, etc. down cells in that column.
Next column will track the digit distribution for the first ball slot...and would contain this formula sequence:
=COUNTIF(A1:A28;0)
=COUNTIF(A1:A28;1)
=COUNTIF(A1:A28;2)
=COUNTIF(A1:A28;3)
=COUNTIF(A1:A28;4)
=COUNTIF(A1:A28;5)
=COUNTIF(A1:A28;6)
=COUNTIF(A1:A28;7)
=COUNTIF(A1:A28;8)
=COUNTIF(A1:A28;9)
then simply copy & paste & mod formulas in additional columns to track the column B & C draw history.
then add another column which will sum up how many times each digit appears across the 3slots combined.
you could actually try to chart that stuff...but seeing the raw digits is more appealing to me instead of a pie chart or whatever.
cheers!
blitzed

03302008, 12:39 AM

Registered User


Join Date: Mar 2008
Posts: 3


Thanks guys! I've tried both ways and they both work great!
Thank again for the help!
Satch

03302008, 12:49 AM


Registered User


Join Date: Oct 1999
Location: Montréal
Posts: 715


Improved spreadsheet
As an afterthought, you could improve the method I gave you to include future draws in the analysis (both the values in the range G4:I13 or the graph). Just include extra lines (currently blank) in the ranges under the actual data. Adjust accordingly the ranges in the data series for the graph.
As you add data to your database, these values will be automatically included in the analysis.

03302008, 01:34 AM


Registered User


Join Date: Jul 2006
Location: Encinitas, CA U$A
Posts: 4,787


Quote:
Originally Posted by Satch
Thanks guys! I've tried both ways and they both work great!
Thank again for the help!
Satch

Satch, another idea is to apply conditional format on the total column...make it bold anything below a certain value so you know that what ever is in your DIGIT column is due for play at a glance.
also, if you use OpenOffice Calc for spreadsheets, you can calculate probabilities with the B function. I don't know if Excel has a binomial distribution function.
this formula for example
=B(24; 3/10; 7)
displays a 17.61% probability that a digit would be drawn 7times total across the slots. here is the probability chart across the slots at 24draws:
0x 0.02%
1x 0.20%
2x 0.97%
3x 3.05%
4x 6.87%
5x 11.77%
6x 15.98%
7x 17.61%
8x 16.04%
9x 12.22%
10x 7.85%
11x 4.28%
12x 1.99%
alternatively you could track the probability on an individual ballslot by just changing 3/10 to 1/10:
=B(24; 1/10; 4) shows 12.92% probability of a digit bein drawn 4x in a slot within 24draws.
cya,
blitzed

03302008, 04:49 AM


Registered User


Join Date: Mar 2004
Location: US
Posts: 388


Quote:
Originally Posted by blitzed
I don't know if Excel has a binomial distribution function.

blitzed 
=BINOMDIST
gl
j

03302008, 01:15 PM

Registered User


Join Date: Mar 2008
Posts: 3


Blitzed,
I am not sure that I completely understand your equation but I am using Calc to make the spreadsheet so I will plug it in and see what it shows me. Thanks for the tip!
Satch

03302008, 01:24 PM


Registered User


Join Date: Jul 2006
Location: Encinitas, CA U$A
Posts: 4,787


Quote:
Originally Posted by johnph77
blitzed 
=BINOMDIST
gl
j

hiya John, thanx for the info
I haven't really used Excel in years, I even use OpenOffice Calc at work...for what I use it for, I can work in it more efficiently, and built in compression is nice too
cya,
blitzed

03302008, 01:38 PM


Registered User


Join Date: Jul 2006
Location: Encinitas, CA U$A
Posts: 4,787


Quote:
Originally Posted by Satch
Blitzed,
I am not sure that I completely understand your equation but I am using Calc to make the spreadsheet so I will plug it in and see what it shows me. Thanks for the tip!
Satch

Hiya Satch,
here is a breakdown of the formula: =B(24; 1/10; 4)
24 is the number of draws
1/10 means that the ballslot can contain 1 of 10 numbers in the pick3 game pool.
4 means that it will calculate the percent probability of a digit being drawn 4times out of 24draws in one ballslot.
the formula with 3/10 isn't really accurate, since pick3 games are only 3 of 10 numbers part of the time since doubles & triples are allowed. ifya wanted a formula for a game such as lotto649, then it'd be 6/49 since each ball number is unique.
blitzed

08122008, 07:49 AM

Registered User


Join Date: Feb 2003
Location: Kent, England
Posts: 180


Excel
On another Excel question, I haven't used it for a long time
How do I search an excel database of lotto numbers, one number in each cell.
I want to look for, say, three numbers in the database of a six number lottery history, ie rows of 6 numbers each in its own cell?
Cheers
Barge

08122008, 07:12 PM


Registered User


Join Date: Oct 1999
Location: Montréal
Posts: 715


Looking for 3 numbers in a lotery database
I am not too sure I fully understand your question but this might provide some help.
Let's say your database is constructed with draw number in column A, date in column B and the six winning numbers in columns C to H (and maybe the bonus number in column I) with titles on row #1 and actual values starting on row #2. You could place in cells L1, M1 and N1 the 3 numbers you are looikng for and in cell J2, enter the formula =COUNTIF(C2:H2,$L$1)+COUNTIF(C2:H2,$M$1)+COUNTIF(C 2:H2,$N$1). Then copy it down for all rows of your database.
You can then use the Automatic filter command on colum J to identify all rows with a value of 3.
If you want to include the bonus number in your search, change the C2:H2 reference to C2:I2.
Basically, this can be adapted for any numbers (up to 6 using cells O1, P1 and Q1) and adding extra COUNTIF functions in cell J2. If you want to look for less than 6 numbers, place a 0 in cells not required (like in P1 and Q1 if looking for 4 numbers in your database). Numbers in L1 to P1 do not have to be in any specific order.
Hope this helps.

08132008, 01:50 PM

Registered User


Join Date: Feb 2003
Location: Kent, England
Posts: 180


To GillesD
Hi G,
Many thanks this is exactly what I was looking for. I just use a "bare" database, a la Saliu, so I am only concerned with the numbers. I can find pairs and trips easily, but I want to find specific groups.
Thanks again
Cheers
Barge

Thread Tools 

Display Modes 
Linear Mode

Posting Rules

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off



All times are GMT 5. The time now is 08:34 PM.

Play Huge Lottos Now!
Get Your Best Bet Days
Play All International Lotteries
