For the fellow who wanted this, I will try to spell it out.

Sheet "weekly numbers" contains the weekly lottery results
with column A being the week, and columns B thru G having
the six numbers. Row 3 has headings, and the data starts
in row 5. This data set must be sorted with most recent
week at top (row 5).

Sheet "analysis" is set up this way:

cell A1 has a count of the total number of weeks of
results.

cell B1 contains input - the last N weeks you want to
search for counting occurances - I used 5.

cells B5:B54 contain the individual numbers to search for -
I used 1 thru 50.

cell C5 contains the following formula.

=COUNTIF('weekly numbers'!$B$5:OFFSET('weekly numbers'!
$B$4,analysis!$B$1,COUNTA('weekly numbers'!$B$3:$H$3)-
1,1,1),$B5)

copy this down thru row 54

the counta function could be replaced with the value 6 if
you want to fix the number of weekly numbers (the number
of lottery numbers), or you could just simply define the
countif range.

cell E5 contains the following formula

=IF(ISNA(MATCH($B5,'weekly numbers'!B$5:OFFSET('weekly
numbers'!B$4,analysis!$A$1+1,0,1,1),0)),"",MATCH
($B5,'weekly numbers'!B$5:OFFSET('weekly numbers'!
B$4,analysis!$A$1+1,0,1,1),0))

copy this down thru row 54 and out thru column J to look
at all six columns of all 50 numbers.

cell C5 contains the following formula

=IF(MIN(E5:I5)=0,"",MIN(E5:J5))

copy this down thru row 54

the result will be column B having the number of hits of
all the numbers in the last N weeks (N being the value in
cell B1), and column C having the number of weeks since
the last occurance of each number.

My workbook seems to work fine.

Well thank you very much, anonymous John, for spelling it out to me. I
was all much clearer then the first time. And it works fine, it eve
gives me more results than I asked for. Is it a stupid question if
ask if its possible to calculate the winning numbers???? I will spli
the jackpot!!!!!

Thanks.
M

--
Message posted from http://www.ExcelForum.com

Well thank you very much, for spelling it out to me. It was all muc
clearer then the first time. And it works fine, it even gives me mor
results than I asked for. Is it a stupid question if I ask if it
possible to calculate the winning numbers???? I will split th
jackpot!!!!!

Thanks.
M

--
Message posted from http://www.ExcelForum.com

"knoertje " wrote...
. . . Is it a stupid question if I ask if its
possible to calculate the winning numbers???? . . .

If it were, there'd be very few working mathematicians or statisticians, and
I doubt the organizations running lotteries would continue to do so.

Winning lottery numbers are purely random. No amount of analysis will
improve your odds. There's only one way to improve your odds of winning: bet
on as many *distinct* sets of numbers as you can afford. There's only one
way to maximize your expected wealth: don't bet on any lottery ever. If your
feelings win out over rationality, bet on 5 distinct sets of numbers chosen
at random when the jackpot exceeds a certain value ($25 million, say).