Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting When Last Occurance
Hi everyone,
I have a table of lottery numbers where 6 balls are drawn each draw. The table is from D3:i whatever and obviously grows constantly. The draw number is in column B with the last draw at the bottom. What I would like is to list the numbers from 1 to 49 starting in S3 and going down and next to each number have the number of draws since that particular number has been drawn please. So basically, start at the bottom right cell of the table and work left and then up to find out how many draws it has been since each of the numbers from 1 to 49 have been drawn. Thanks in advance. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting When Last Occurance
On Jul 15, 2:18*pm, Paul Black wrote:
Hi everyone, I have a table of lottery numbers where 6 balls are drawn each draw. The table is from D3:i whatever and obviously grows constantly. The draw number is in column B with the last draw at the bottom. What I would like is to list the numbers from 1 to 49 starting in S3 and going down and next to each number have the number of draws since that particular number has been drawn please. So basically, start at the bottom right cell of the table and work left and then up to find out how many draws it has been since each of the numbers from 1 to 49 have been drawn. Thanks in advance. Paul Has anyone got any ideas please. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting When Last Occurance
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jul 15, 2:18 pm, Paul Black wrote: Hi everyone, I have a table of lottery numbers where 6 balls are drawn each draw. The table is from D3:i whatever and obviously grows constantly. The draw number is in column B with the last draw at the bottom. What I would like is to list the numbers from 1 to 49 starting in S3 and going down and next to each number have the number of draws since that particular number has been drawn please. So basically, start at the bottom right cell of the table and work left and then up to find out how many draws it has been since each of the numbers from 1 to 49 have been drawn. Thanks in advance. Paul Has anyone got any ideas please. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting When Last Occurance
On Jul 16, 2:47*pm, "Don Guillett" wrote:
* * * If desired, send your file to my address below along with this msg and a clear explanation of what you want and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jul 15, 2:18 pm, Paul Black wrote: Hi everyone, I have a table of lottery numbers where 6 balls are drawn each draw. The table is from D3:i whatever and obviously grows constantly. The draw number is in column B with the last draw at the bottom. What I would like is to list the numbers from 1 to 49 starting in S3 and going down and next to each number have the number of draws since that particular number has been drawn please. So basically, start at the bottom right cell of the table and work left and then up to find out how many draws it has been since each of the numbers from 1 to 49 have been drawn. Thanks in advance. Paul Has anyone got any ideas please. Thanks, I will put something together over the weekend if possible. Regards, Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting When Last Occurance
On Wed, 15 Jul 2009 06:18:14 -0700 (PDT), Paul Black
wrote: Hi everyone, I have a table of lottery numbers where 6 balls are drawn each draw. The table is from D3:i whatever and obviously grows constantly. The draw number is in column B with the last draw at the bottom. What I would like is to list the numbers from 1 to 49 starting in S3 and going down and next to each number have the number of draws since that particular number has been drawn please. So basically, start at the bottom right cell of the table and work left and then up to find out how many draws it has been since each of the numbers from 1 to 49 have been drawn. Thanks in advance. Paul If you have the number 1,2,3, ..., 49 in cells S3, S4, S5, ... S51 you may try the following formula in cell T3: =IF(OR(D$3:I$1000=S3),MAX(ROW(D$3:D$1000)*(D$3:D$1 000<""))-MAX(ROW(D$3:I$1000)*(MMULT(--(D$3:I$1000=S3),{1;1;1;1;1;1})0)),"newer drawn") Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Change the 1000 to suit the size of your data in columns D to I. Copy the formula down until cell T51. The numbers in column T now show the number of draws since the corresponding numbers in column S were drawn. 0 means that the number was in the last draw. If a number has never been drawn, the text "never drawn" is displayed in column T. Hope this helps / Lars-Åke |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting When Last Occurance
On Jul 17, 1:26*pm, Lars-Åke Aspelin
wrote: On Wed, 15 Jul 2009 06:18:14 -0700 (PDT),PaulBlack wrote: Hi everyone, I have a table of lottery numbers where 6 balls are drawn each draw. The table is from D3:i whatever and obviously grows constantly. The draw number is in column B with the last draw at the bottom. What I would like is to list the numbers from 1 to 49 starting in S3 and going down and next to each number have the number of draws since that particular number has been drawn please. So basically, start at the bottom right cell of the table and work left and then up to find out how many draws it has been since each of the numbers from 1 to 49 have been drawn. Thanks in advance. Paul If you have the number 1,2,3, ..., 49 in cells S3, S4, S5, ... S51 you may try the following formula in cell T3: =IF(OR(D$3:I$1000=S3),MAX(ROW(D$3:D$1000)*(D$3:D$1 000<""))-MAX(ROW(D$3:I$1*000)*(MMULT(--(D$3:I$1000=S3),{1;1;1;1;1;1})0)),"newer drawn") Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Change the 1000 to suit the size of your data in columns D to I. Copy the formula down until cell T51. The numbers in column T now show the number of draws since the corresponding numbers in column S were drawn. 0 means that the number was in the last draw. If a number has never been drawn, the text "never drawn" is displayed in column T. Hope this helps / Lars-Åke Hi Lars-Åke, Your formula works great thank you. Regards, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting occurance of empty cells | Excel Discussion (Misc queries) | |||
Counting occurance of 0 in two columns | Excel Worksheet Functions | |||
Counting the Excced occurance... | Excel Discussion (Misc queries) | |||
Occurance Counting | Excel Worksheet Functions | |||
counting occurance of a name in a cell | Excel Programming |