Number Search In Lottery Number Sets

J

JAgger1

5 12 22 28 45 49
1 15 16 32 33 48
4 18 23 33 37 41
11 12 38 41 45 47
1 12 15 21 31 49
8 11 25 26 39 41

Using the list of numbers as an example, from top to bottom, what
formulas would I use to search for a set of 3 numbers (ie 15 16 33 in
the second set of numbers) and then tell me how many sets it has been
since it occurred? The 3 numbers could be in any of the 6 position
within the set, and I'm going to be using roughly a years worth of
sets, so 365 sets? Thanks
 
M

Max

First, split the data into 6 cols via Data > Text to Columns (delimited >
space)
Assume data is now split in cols A to F, from row 1 down
Put your 3 base nums into I1:I3, eg: 15, 16, 33
Then place in G1:
=SUMPRODUCT(--ISNUMBER(MATCH($I$1:$I$3,A1:F1,0)))
Copy G1 down all the way to return the number of matches for each set in
cols A to F. Then use col G's returns as a startpoint to do whatever
downstream analysis that's desired in adjacent cols, etc (leave this to you,
I won't know).
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
 
T

T. Valko

Try these:

To count how many times those numbers were drawn:

Assume drawn numbers in the range B2:G366

15, 16, 33 in cells I2:K2

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})=3))

To get the number of draws between the last occurrence and the 2nd to last
occurrence:

Array entered** :

=MAX((MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})=3)*ROW(B2:G366))-LARGE((MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})=3)*ROW(B2:G366),2)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Note that if the numbers have not been drawn at least twice the formula will
return an error.
 
J

JAgger1

Excellent, thanks for you help.

Now, if the number set I'm looking for hasn't shown up in the last say
50 draws, using the number range B2:G366, how would I write the array/
formula to tell me that? Does the number set need to have occured at
least twice before? Or do I need only one occurence?
 
J

JAgger1

Also, Max, If I hit 3 numbers in a set, how can I have the cell (in
the G column) change color, say to red?

T. Valko, if the number of days between occurrences is greater than
say 100 days, how would I add to the array so I have the results in
the cell change color as well, say red to let me know it's been over
100 days, as an example? Thanks
 
J

JAgger1

Sorry, forget last request, I forgot that I can change cell result
colors with Conditional Formatting.
 
T

T. Valko

Ok, it'll take a couple of steps.

Assume you use this formula to get the count of the number of times this
combo has been drawn:

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})=3))

Let's assume that formula is in cell M1.

Use this array formula** to get the row number of the last instance of this
combo:

=MAX((MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})=3)*ROW(B2:G366))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Let's assume that formula is in cell M2.

Enter this formula in M3 to get the number of draws since the combo last
hit:

=IF(M1>0,COUNT(G2:G366)+1-M2,"no hits")
 
J

JAgger1

Hmmm, I keep getting the error "Array formulas are not valid in merged
cells" for

=MAX((MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})=3)*ROW
(B2:G36­6))
 
J

JAgger1

If I use the formula:

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})
=3))

To count how many times my numbers were drawn inthe last 2 years, and
say those numbers were drawn 5 times in those 2 years.

How would I write/modify the following Array to get the number of
draws between each of those 5 occurence's?

=MAX((MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})=3)*ROW
(B2:G36­6))-LARGE((MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),
{1;1;1;1;1;1})=3)*ROW(B­2:G366),2)
 
T

T. Valko

Let's assume you want the results in N2 and copied down.

Array entered:

=LARGE((MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})=3)*ROW
(B2:G36­6),ROWS(N$2:N2))-LARGE((MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),
{1;1;1;1;1;1})=3)*ROW(B­2:G366),ROWS(N$2:N3))


--
Biff
Microsoft Excel MVP


If I use the formula:

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})
=3))

To count how many times my numbers were drawn inthe last 2 years, and
say those numbers were drawn 5 times in those 2 years.

How would I write/modify the following Array to get the number of
draws between each of those 5 occurence's?

=MAX((MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),{1;1;1;1;1;1})=3)*ROW
(B2:G36­6))-LARGE((MMULT(--ISNUMBER(MATCH(B2:G366,I2:K2,0)),
{1;1;1;1;1;1})=3)*ROW(B­2:G366),2)
 
T

T. Valko

Ooops!

You'll have to make the range references absolute:

=LARGE((MMULT(--ISNUMBER(MATCH(B$2:G$366,I$2:K$2,0)),{1;1;1;1;1;1})=3)*ROW
(B$2:G$36­6),ROWS(N$2:N2))-LARGE((MMULT(--ISNUMBER(MATCH(B$2:G$366,I$2:K$2,0)),
{1;1;1;1;1;1})=3)*ROW(B$­2:G$366),ROWS(N$2:N3))
 
D

Dave Peterson

I don't see anything in Biff's last suggestion that would cause that error.

My bet is that you have a typo in your formula.

Biff did have a couple of other typos (some how dashes got added):

=LARGE((MMULT(--ISNUMBER(MATCH(B$2:G$366,I$2:K$2,0)),{1;1;1;1;1;1})=3)*ROW
(B$2:G$366),ROWS(N$2:N2))-LARGE((MMULT(--ISNUMBER(MATCH(B$2:G$366,I$2:K$2,0)),
{1;1;1;1;1;1})=3)*ROW(B$2:G$366),ROWS(N$2:N3))

So the next question is what language are you using?
And what do you use for the windows list separator?

And if that doesn't help, post the formula that causes the error.
 
J

JAgger1

Hmm

I found this reference to Name Errors

If, while working in Excel 97, you enter a formula and all you receive
is a #NAME! message in its place, don't worry. All this means is that
your formula is referring to a name that does not exist in your
spreedsheet. To fix this problem, simply double-check that the name
of the item you are using the formula with and the name in the formula
are one and the same.

Not sure it it's referring to the LARGE name?
 
D

Dave Peterson

=large() is built into the USA/English version of excel.

Does excel highlight the portion that it thinks is a name?

One more thing to check....

If you're in R1C1 reference style mode (numbers for column indicators, not
letters), then you may see this error. (But I didn't using Biff's formula.)

Tools|Options|General Tab
 
J

JAgger1

Ok, I got it worked out. I copied the command to a text file, made it
one long line, there was a couple of extra spaces causing the problem.
Thanks for your help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help!! find two number recurring numbers in row?? 1
Number Match Formula? 7
Lottery 6
need help finding most common number and so on 2
Excel date formate doesn't change 0
Lookups Help 2
Sumproduct Problem 4
Some kind of vlookup required? 4

Top