Positional Analysis in Excel?

S

Satch

Member
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
 
G

GillesD

Member
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 Shift-Ctrl-Enter 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).
 
blitzed

blitzed

Member
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:thumb:
 
G

GillesD

Member
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.
 
blitzed

blitzed

Member
Satch said:
Thanks guys! I've tried both ways and they both work great!
Thank again for the help!
Satch
Click to expand...

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:thumb:
 
S

Satch

Member
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
 
blitzed

blitzed

Member
johnph77 said:
blitzed -

=BINOMDIST

gl

j
Click to expand...

hiya John, thanx for the info:agree:

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:thumb:
 
blitzed

blitzed

Member
Satch said:
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
Click to expand...

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:thumb:
 
B

barge

Member
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
 
G

GillesD

Member
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(C2: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.
 
B

barge

Member
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
 
You must log in or register to reply here.

Sidebar

Top