#### Frank

##### Member

**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.

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.

**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....