Frank
Member
As discussed in another thead the monitoring of skip values for lottery balls can be done in one of three ways. One is the simple noting of the draw number it is drawn in and calculation of skips directly from that. Another way would be to note down the result in DRAWN order and then calculate skip values for each ball in its DRAWN position, creating N different
skip values for each ball if N balls are drawn . With this method all balls are equal in that the probability of a ball being drawn first , second, or last is identical for each ball. Therefore the average theoretical skip is identical for each ball.
Then there is a third method. Monitoring the skip values of each ball in each position when the result has been sorted in ascending order. It is important to be aware that not all balls are equal in each sorted position because the act of sorting creates a probability distribution as to where in order a ball can possibly be after sorting, depending on the values of the other balls. With ball number 1 it is easy to see that it can only be in first position and can only have a skip value in first position.
The below image shows what I mean for a 6/49 lottery. If you were to examine every possible lottery ticket each number can only appear when sorted - that number of times in that position. This has a direct effect on how long you would need to wait to see a number drawn in that sorted position.
http://i41.tinypic.com/111uu61.gif
From such a table, probabilities and theoretical skips follow. So it is important to know the theoretical skip values if you monitor the actual skips on a positional basis on sorted results as they are all different.
I intend to show here the steps required to create a table suitable for most lotteries, you just enter the size:- total number of balls and the number of balls drawn, and the sheet does the rest.
First a note about Excel compatibility. As the active size of the tables are unknown until you enter the two parameters defining your lottery, the sheet was designed to be big enough to cover 59 balls and up to 7 balls drawn. In most cases some rows and columns will be redundant and would display an "#NUM!" message in the cells of redundant rows/columns. To stop this I have used the ISERROR function (Excel 98-2003) to screen for them and blank them out. For later versions of Excel there is a more compact way of dealing with it using the newer IFERROR function. As I said, for compatibility back to Excel 2000 I will be using the lengthier ISERROR method.
OK here goes. The above image should aid understanding.
Starting with a blank spreadsheet
STAGE 1
Allocate 2 cells G4 for the number of balls drawn (up to 7)... and H4 for total number of balls in the pool (up to 59). Put a bold border around the cells, perhaps highlight their background colour so they are clear. Just for now , so we are on the same page Enter 6 in G4 and 49 in H4. You can change this later when we are all done. As the sheet evolves we will see figures for a 6/49 lottery.
Now we require to construct a small reference table (I'll explain what it does later)
In cell C6 enter the formula =$G4-1.
In cell D6 enter the formula =C6-1
Copy the formula in D6 across to column I to cell I6. You will probably see ,5,4,3,2,1,-1 in these cells now. Bear with me..
In Cell C7 enter the value 0 (zero). In Cell D7 enter value 1 , in E7 enter 2, in F7 enter 3 and so on .. (numbers 0 to 6 along row 7. I7 will contain value 6 ) Put borders around the cells (like in the image above).
In Cell J4 enter the formula =COMBIN(H4,G4) this is the number of combinations for the lottery and will be used in other formulas. Label it or highlight it. Its an important cell.
The main Ball count table is directly below using columns B to J.
(you may wish to skip this explanation until the table is completed and go straight to consructing the main table)
Now the mini table above (C6 to I7) works out how many columns there are to the left or right of each ball position. For example in the fifth sorted position (column G) if you look above, it shows a 4 and 1, meaning there are 4 columns to the left and 1 (valid) column to the right of that position for that number of balls drawn. Main formulas need this information later.
The formulas in the main table work out how many balls are lower than the ball number we are considering, find out how many places are available to the left of 'where we are now' for those numbers to sit in and works out a combinational figure for how many 'n tuplet' combinations can be less whilst this number sits here. They also work out how many numbers are higher than the number we are considering, find how many places are available for them to sit in and work out another combinational figure of all the possible n_tuplets there can be from this position onwards starting with this ball in this position.
Finally they multiply the two figures together to work out how many times the number we are considering can be here in this position, surrounded by all the available other numbers. Its bit hard to get your head around, but the formulas work, thats the main thing.
Now for the main ball count/positional table ...
First we need a list of ball numbers in column B
In cells B10 down to B68 enter ball numbers 1,2,3......59. Perhaps highlight them in red.
Right now for the Main table formulas. Copy this to your clipboard,......
=IF(ISERROR(COMBIN($B10-1,C$7)*COMBIN($H$4-$B10,C$6)),"",COMBIN($B10-1,C$7)*COMBIN($H$4-$B10,C$6))
Click on cell C10 then Click your mouse in the formula bar and paste that formula in, press <enter>
Now copy the formula across row 10 to cell I10 ( you will not see anything appear though).
Select cells C10:I10 and copy the formula down to row 68 thus filling C10:I68 with formulas. Note that the last position column (column I) will look empty because for 6/49 there is no 7th position.
In cell J10 enter (or use copy/paste as above) :- =SUM(C10:I10)
Copy that down column J to cell J68. All the values should be the same in this column, it checks all is well with the values in the other columns if the sums here are equal. Enter a label in Cell A72 "Totals"
In Cell C72 enter the formula =SUM(C10:C71). Copy this formula along row 72 to Cell I72. So now C72:I72 will display
totals for each column. Again they should all be equal if all is well.
Note the diagonal stepping at the top and bottom of the ball numbers to reflect the impossibility of certain balls appearing in certain columns when sorted ascending.
Finally some sorted position headings :- in Cell B9 enter "N" , and in cells Cells C9 through to I9 enter labels A,B,C,D,E,F,G as per the image. FORMAT the headings appropriately.
In cell J9 enter "TOTAL"
This table is now complete. You should now see the number count distribution for all the numbers in all column positions.
All that remains is to format it in approprate colours and do cell borders etc. Add The table title THEORETICAL NUMBER OF OCURRENCES OF SORTED NUMBER N IN COLUMN SHOWN .
If you did it right, it will look similar to my image. For readability I will continue with the next table in the next post....
skip values for each ball if N balls are drawn . With this method all balls are equal in that the probability of a ball being drawn first , second, or last is identical for each ball. Therefore the average theoretical skip is identical for each ball.
Then there is a third method. Monitoring the skip values of each ball in each position when the result has been sorted in ascending order. It is important to be aware that not all balls are equal in each sorted position because the act of sorting creates a probability distribution as to where in order a ball can possibly be after sorting, depending on the values of the other balls. With ball number 1 it is easy to see that it can only be in first position and can only have a skip value in first position.
The below image shows what I mean for a 6/49 lottery. If you were to examine every possible lottery ticket each number can only appear when sorted - that number of times in that position. This has a direct effect on how long you would need to wait to see a number drawn in that sorted position.
http://i41.tinypic.com/111uu61.gif
From such a table, probabilities and theoretical skips follow. So it is important to know the theoretical skip values if you monitor the actual skips on a positional basis on sorted results as they are all different.
I intend to show here the steps required to create a table suitable for most lotteries, you just enter the size:- total number of balls and the number of balls drawn, and the sheet does the rest.
First a note about Excel compatibility. As the active size of the tables are unknown until you enter the two parameters defining your lottery, the sheet was designed to be big enough to cover 59 balls and up to 7 balls drawn. In most cases some rows and columns will be redundant and would display an "#NUM!" message in the cells of redundant rows/columns. To stop this I have used the ISERROR function (Excel 98-2003) to screen for them and blank them out. For later versions of Excel there is a more compact way of dealing with it using the newer IFERROR function. As I said, for compatibility back to Excel 2000 I will be using the lengthier ISERROR method.
OK here goes. The above image should aid understanding.
Starting with a blank spreadsheet
STAGE 1
Allocate 2 cells G4 for the number of balls drawn (up to 7)... and H4 for total number of balls in the pool (up to 59). Put a bold border around the cells, perhaps highlight their background colour so they are clear. Just for now , so we are on the same page Enter 6 in G4 and 49 in H4. You can change this later when we are all done. As the sheet evolves we will see figures for a 6/49 lottery.
Now we require to construct a small reference table (I'll explain what it does later)
In cell C6 enter the formula =$G4-1.
In cell D6 enter the formula =C6-1
Copy the formula in D6 across to column I to cell I6. You will probably see ,5,4,3,2,1,-1 in these cells now. Bear with me..
In Cell C7 enter the value 0 (zero). In Cell D7 enter value 1 , in E7 enter 2, in F7 enter 3 and so on .. (numbers 0 to 6 along row 7. I7 will contain value 6 ) Put borders around the cells (like in the image above).
In Cell J4 enter the formula =COMBIN(H4,G4) this is the number of combinations for the lottery and will be used in other formulas. Label it or highlight it. Its an important cell.
The main Ball count table is directly below using columns B to J.
(you may wish to skip this explanation until the table is completed and go straight to consructing the main table)
Now the mini table above (C6 to I7) works out how many columns there are to the left or right of each ball position. For example in the fifth sorted position (column G) if you look above, it shows a 4 and 1, meaning there are 4 columns to the left and 1 (valid) column to the right of that position for that number of balls drawn. Main formulas need this information later.
The formulas in the main table work out how many balls are lower than the ball number we are considering, find out how many places are available to the left of 'where we are now' for those numbers to sit in and works out a combinational figure for how many 'n tuplet' combinations can be less whilst this number sits here. They also work out how many numbers are higher than the number we are considering, find how many places are available for them to sit in and work out another combinational figure of all the possible n_tuplets there can be from this position onwards starting with this ball in this position.
Finally they multiply the two figures together to work out how many times the number we are considering can be here in this position, surrounded by all the available other numbers. Its bit hard to get your head around, but the formulas work, thats the main thing.
Now for the main ball count/positional table ...
First we need a list of ball numbers in column B
In cells B10 down to B68 enter ball numbers 1,2,3......59. Perhaps highlight them in red.
Right now for the Main table formulas. Copy this to your clipboard,......
=IF(ISERROR(COMBIN($B10-1,C$7)*COMBIN($H$4-$B10,C$6)),"",COMBIN($B10-1,C$7)*COMBIN($H$4-$B10,C$6))
Click on cell C10 then Click your mouse in the formula bar and paste that formula in, press <enter>
Now copy the formula across row 10 to cell I10 ( you will not see anything appear though).
Select cells C10:I10 and copy the formula down to row 68 thus filling C10:I68 with formulas. Note that the last position column (column I) will look empty because for 6/49 there is no 7th position.
In cell J10 enter (or use copy/paste as above) :- =SUM(C10:I10)
Copy that down column J to cell J68. All the values should be the same in this column, it checks all is well with the values in the other columns if the sums here are equal. Enter a label in Cell A72 "Totals"
In Cell C72 enter the formula =SUM(C10:C71). Copy this formula along row 72 to Cell I72. So now C72:I72 will display
totals for each column. Again they should all be equal if all is well.
Note the diagonal stepping at the top and bottom of the ball numbers to reflect the impossibility of certain balls appearing in certain columns when sorted ascending.
Finally some sorted position headings :- in Cell B9 enter "N" , and in cells Cells C9 through to I9 enter labels A,B,C,D,E,F,G as per the image. FORMAT the headings appropriately.
In cell J9 enter "TOTAL"
This table is now complete. You should now see the number count distribution for all the numbers in all column positions.
All that remains is to format it in approprate colours and do cell borders etc. Add The table title THEORETICAL NUMBER OF OCURRENCES OF SORTED NUMBER N IN COLUMN SHOWN .
If you did it right, it will look similar to my image. For readability I will continue with the next table in the next post....
.
.