Pick-3 Lottery Formula

[robgrant]

Here's the scoop:

I have an Excel Workbook with 5 years worth of pick-3 lottery data arranged in 4 columns: date (A4), pos 1 digit (B4), pos 2 digit (C4), pos 3 digit (D4).

I'd like to build a table that would show how many drawings it's been since each digit has been draw in each position. For example:


__|_1 pos_______|_2 pos_______|_3 pos_____

0 | #draws | #draws | #draws
1
2
3
4
5
6
7
8
9


The table should report the number of draws since last draw. So, if a number in a given position was drawn today, the table would report the number of draws as 0. If the number 8 as the first digit was drawn 3 days ago, the table would report a 3 in that cell.

 

[robgrant]

Re: Help with Pick-3 Lottery Formula

same thing as before. #Value fills the cells.

 

[barry houdini]

Re: Help with Pick-3 Lottery Formula

OK, that works on my test sheet but you might have something on yours that doesn't quite match. As a test what happens if you try just the INDEX part of the formula in B2 copied down and across, i.e.

=INDEX(Data!$A$4:$A$2000,MATCH($A2,Data!B$4:B$2000,0))

I expect it to just pick up the value from column A for the relevant row so for B2 you should get

06/27/2010 Evening

 

[robgrant]

Re: Help with Pick-3 Lottery Formula

I thought I PM'd you that I removed the "evening" text from the date and set it to general format.


Here's what I get with your latest formula suggestion:

 

[robgrant]

Re: Help with Pick-3 Lottery Formula

It appears the cells contain the last dates when the digits were drawn. So how do I now convert the date to a number of days?

 

[robgrant]

Re: Help with Pick-3 Lottery Formula

Hi,

I re-did the dataset. Forgot that State Lottery had the data as a hyperlink and I converted to text. So, I made my own column of dates.

Next, I used you last formula and made a matrix that subtracts the result from today's date formatted as general.

Here's the result: