Lotto Forums
How To Win

Go Back   Lotto Forums > Lotto Forums Players Toolbox > Lotto Tips & Strategies

Lotto Tips & Strategies Methods and Systems for Winning the Jackpot.

Reply
 
Thread Tools Display Modes
  #1  
Old 03-29-2008, 03:47 PM
Satch Satch is offline
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
Reply With Quote
  #2  
Old 03-29-2008, 10:43 PM
GillesD's Avatar
GillesD GillesD is offline
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 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).
Reply With Quote
  #3  
Old 03-29-2008, 11:18 PM
blitzed's Avatar
blitzed blitzed is offline
Registered User
 
Join Date: Jul 2006
Location: Encinitas, CA U$A
Posts: 4,347
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
Reply With Quote
  #4  
Old 03-29-2008, 11:39 PM
Satch Satch is offline
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
Reply With Quote
  #5  
Old 03-29-2008, 11:49 PM
GillesD's Avatar
GillesD GillesD is offline
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.
Reply With Quote
  #6  
Old 03-30-2008, 12:34 AM
blitzed's Avatar
blitzed blitzed is offline
Registered User
 
Join Date: Jul 2006
Location: Encinitas, CA U$A
Posts: 4,347
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
Reply With Quote
  #7  
Old 03-30-2008, 03:49 AM
johnph77's Avatar
johnph77 johnph77 is offline
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
Reply With Quote
  #8  
Old 03-30-2008, 12:15 PM
Satch Satch is offline
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
Reply With Quote
  #9  
Old 03-30-2008, 12:24 PM
blitzed's Avatar
blitzed blitzed is offline
Registered User
 
Join Date: Jul 2006
Location: Encinitas, CA U$A
Posts: 4,347
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
Reply With Quote
  #10  
Old 03-30-2008, 12:38 PM
blitzed's Avatar
blitzed blitzed is offline
Registered User
 
Join Date: Jul 2006
Location: Encinitas, CA U$A
Posts: 4,347
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
Reply With Quote
  #11  
Old 08-12-2008, 06:49 AM
barge barge is offline
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
Reply With Quote
  #12  
Old 08-12-2008, 06:12 PM
GillesD's Avatar
GillesD GillesD is offline
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.
Reply With Quote
  #13  
Old 08-13-2008, 12:50 PM
barge barge is offline
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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA Program for Wheel Analysis PAB Lotto Software 1 12-17-2007 09:36 PM
Positional analysis Bertil Lotto 649 34 01-23-2007 11:04 AM
Positional Analysis for Odds & Evens PAB Lotto Tips & Strategies 4 04-25-2005 04:33 AM
positional analysis for super 7 luckyhorse Questions & Answers 11 04-18-2005 07:32 PM
positional analysis for super 7 luckyhorse Questions & Answers 6 03-29-2004 10:10 PM


All times are GMT -5. The time now is 05:07 PM.

Silver Lotto System
Silver Lotto System

 

Free Lottery Secrets Report
Free Lottery Secrets Report

 


Get Your Best Bet Days

 

 

Lotto Pro

Powered by vBulletin
Copyright © 2000 - 2014 Jelsoft Enterprises Ltd.
Copyright © 1999 - 2012 LottoForums.com