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.

 

[barry houdini]

Re: Help with Pick-3 Lottery Formula

Hello robgrant, welcome to MrExcel,

If you have 0 to 9 listed in F2 down then put this formula in G2 for position 1 results

=TODAY()-MAX(IF(B$4:B$2000=$F2,$A$4:$A$2000))

That's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

Format cell as number and copy down column.....and across 2 columns for 2 pos and 3 pos

 

[robgrant]

Re: Help with Pick-3 Lottery Formula

I'd like to put this in it's own sheet. Can you give me instructions to do that?

 

[barry houdini]

Re: Help with Pick-3 Lottery Formula

You can use the same formula, essentially, just change the cell refs, e.g. if your data is in a sheet called data then in another sheet list 0 to 9 in A2 down and then in B2 copied across and down to D11

=TODAY()-MAX(IF('Data'!B$4:B$2000=$A2,'Data'!$A$4:$A$2000))

 

[robgrant]

Re: Help with Pick-3 Lottery Formula

No good. Here's what I get:

 

[barry houdini]

Re: Help with Pick-3 Lottery Formula

OK, that looks like the MAX part is just returning a zero value.

Are those columns right - dates are in 'Data'!$A$4:$A$2000

and numbers in columns B, C and D?

Perhaps some of your data is text formatted, does this work?

=TODAY()-MAX(IF('Data'!B$4:B$2000+0=$A2,'Data'!$A$4:$A$2000+0))

 

[robgrant]

Re: Help with Pick-3 Lottery Formula

Now I get this:

 

[barry houdini]

Re: Help with Pick-3 Lottery Formula

OK, the reason for using +0 is to try to convert a text formatted number or date to a numerical value.......but if you apply the +0 to a value which can't be "co-erced" to numeric you'd get a #VALUE error.

Do the dates actually start at A4? What do they look like? What result do you get with

=ISNUMBER(A4)

and

=ISNUMBER(B4)

 

[robgrant]

Re: Help with Pick-3 Lottery Formula

Here's what the data sheet looks like (I didn't understand the 2nd part of your message):

 

[barry houdini]

Re: Help with Pick-3 Lottery Formula

OK, the "Evening" in with the dates would make my suggested formula fail. Try a slightly different approach with this non-array version in B2.

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

format as general then copy down and across.