Excel help with skips and medians

[ganbatte]

I would like to use a macro or formula in Excel to work out the skips and the median of each number's skips in a 6/49 lottery.

I have used GillesD's method below to show all the skips in columns I to BE (numbers 1 to 49) and as GillesD suggested they start from row 3 and run to row 302.

Skips for Pick 3 draws (01-22-2004)

Let’s say your data in Excel has the draw number in column A and N1, N2 and N3 in columns B, C and D starting on row #3 and going down. Row 2 can be used for labels. Note it might be best if the width of columns is small in order to see more (a width of 5 is enough).

A - Skips not considering the position

The method listed here does take into consideration the position a number has come out but simply when it did come out last.

In cells F1 to O1, enter the consecutive numbers, 0,1, 2, …, 9. In cell F3, enter the formula =IF(COUNTIF($B3:$D3,F$1)>=1,0,F2+1). Then copy this formula across to column O, and down for all the draws.

This will give you a 0 if the number in the top row has come out in the draw or otherwise a number indicating how many draws the number has not come out.

What I would like to do is count the number of rows between 0s (zeros) in each column and display those counts in columns BG to DC (numbers 1 to 49). Once that is done then I can easily work out the median of each number with a simple formula.

This macro or formula should work without alterations when I add new draws to the worksheet.

I have been thinking of calculating the address of each cell containing 0s (zeros) and then counting the numbers between them. The major difficulty is working out how to move onto the next address down and so on.

Can anyone assist me with this?

 

[bloubul]

Hi ganbatte

Ek het verskeie van GillesD se spreadsheets.
Praat met Patrick123 dat hy jou goed vir my stuur en so kan ek dan die spreads vir jou stuur.

BlouBul

 

[ganbatte]

I was provided with a formula with the data in column A and the result in column B all starting in row 3.

=IF(ISERROR(MATCH(0,INDEX($A$2:$A$1000,SUM($B$2:B2,2)):$A$1000,0))=TRUE,"",MATCH(0,INDEX($A$2:$A$1000,SUM($B$2:B2,2)):$A$1000,0))