"knoertje " wrote in message
...
On one sheet I keep track of the lottery numbers every week. On another
sheet I try to keep the statistics of these numbers, like how many
times and how many drawings ago a number ........ The how many times
was no problem, with a COUNTIF....but the how many drawings ago,
doesn't work. I tried an IF (sheet1!A1:A6="number",sheet2!A1+1,
sheet2!=1. But then I get a circular reference statement. How do I
proceed?????

I'm not sure if this is what you are hoping to do, but just using worksheet
functions without VBA, this seems to do what I understand you are asking
about.

If you have 5 columns on the worksheet like this:

Col A Col B Col C Col D
Col E
Number Times Drawn Date Weeks Ago #
weeks ago this was drawn

25 2 4/24/04 1
4
31 3 4/17/04 2
3
31 3 4/10/04 3
6
25 2 4/3/04 4
66 3/27/04 5
31 3 3/17/04 6

Col A says what number was drawn. A countif function in col B shows how
many times it has been drawn in previous drawings. Col c is the date of the
drawing, col D is how many weeks agao it the drawing was held and Col E says
if a number was drawn more than once, how many weeks ago from today did it
last get drawn. So the spreadsheet shows that 31 has been drawn 3 times.
The last time it was drawn was 3 weeks ago and the time before that was 6
weeks ago, both calculated from today (i.e. when I say that on April 10th
the number drawn was 31 and the previous instance of 31 was 6 weeks ago, it
is 6 weeks ago today and not 6 weeks ago to April 10).

If this is what you are envisioning, here are the functions I used:
for the countif function in column B:
=IF(COUNTIF(A$2:A$7,A2)1,COUNTIF(A$2:A$7,A2),"")

For the function in column E:
=IF(ISERROR(VLOOKUP(A2,A3:D$7,4,FALSE)),"",VLOOKUP (A2,A3:D$7,4,FALSE))

CA