Count the number of occupied cells in a Named Range between two No's. the same and repeat down the range until a empty cell is found

 Previous | Next
Latest post Sun, Apr 27 2008 10:08 AM by excel_nightmare. 5 replies.

Count the number of occupied cells in a Named Range between two No's. the same and repeat down the range until a empty cell is found

 I  am 57 years old and have just started using Excel 2003 (and hopefully soon to have excel 2007) to keep myself occupied. I have set my self a task and have been creating a worksheet to predict the UK Lottery numbers each week.  I realise that the odds of winning are massive and it is very unlikely for me to actually win this way, but unless I try – who knows. And since the object of the exercise is to teach myself Excel Programming Including VBA (especially since I am actually enjoying it) – then it doesn’t really matter if creating an Excel worksheet that actually cuts the odds of winning or not.  I am working on various ways to reduce the odds including Benford's Law but I am stuck, an was wondering if may be someone could at least point me in the right direction.  What I wish to do is:-  Given a column of numbers in a named array (or what ever) 52 cells long, there are numbers between 1 & 49 with in the cells of this array and since I have just started inputting the data into this list I have only filled in say 10 of the cells out of 52 in this first column.  Now since Bedford Law indicates that the first 30.10299957% of numbers picked start with the letter "1", 17.60912591% start with a "2" and so on, what I would like to do is to find the first "1"in the range (whether it be a "1"or the first "1" in "10" or "15" and so on) and then count the cells occupied with a number between this first "1" and the next 1"" found down the list, and then from the last "1" found to the next down the list and so on until all the 1’s have been found in this column and all the number counted between, until there are no more ones or we reach an empty cell.  I would like to place each count found in separate cells in a named array (or what ever) so that I can analyze in this case, the number of weeks number 1 wasn’t picked.  The same process will then have to be carried out with the numbers 2, 3 & 4. (2, 20, 21…29), (3, 30, 31…….39) and (4, 40, 41…….49).  Since this is an array or list for the first number picked out the machine I would then repeat this process for the next 5 numbers picked from the machine for the next 52 weeks. The first numbers picked are in column One, the second number picked are in column two and so on until you have six columns. Six being the number of balls picked (I don’t concern myself with the bonus ball) each week to make up the Lottery draw.  I believe that VBA is possibly the way to go for this function although to  be quite honest I  haven’t got a clue until I do it so if anyone has a solutions, suggestions or can point me in the right direction I would be very much appreciate it.
Filed under: , ,
• Post Points: 21

Re: Count the number of occupied cells in a Named Range between two No's. the same and repeat down the range until a empty cell is found

 What do you know already? Have you got a design for the code, even in high-level English terms? Can you construct a loop to look through each cell? Do you know how to extract the first character of a cell's reference? Regards Bob
• Post Points: 21

Re: Count the number of occupied cells in a Named Range between two No's. the same and repeat down the range until a empty cell is found

Although I am a beginner I have been producing basic Excel worksheets for serveral years using some of the most common functions.

Since I've always wanted to improve my Excels Skills - I though what better than to create a more complicated worksheet now I have time due to illness.

Apart from my recent posting I have only an idea in my head and an actual working worksheet - but it now appears that I have a lot more to learn about the way in which I set about achieving the result I would like.

I have got has far as using the following formula:-

To find the address of the first Number, where K77 in this case is the number I am looking for to start my count and Latest1 is a Named Range of a single column 52 cells long. Which has the latest data including this weeks result for the first ball picked. However at present I only a few cells filled as I am still developing the worksheet and have just started recording the results.

The First Workshhet contains the following:-

I have Named Ranges which include Latest1 ...to Latest6(the last filled cell in Latest1 being this weeks 1st number picked and the cell above that , the 1st number picked last weeks so on).

The next Named Range is called Previous1... to Previous6.

Previous1 data is exactly the same as Latest1 with the exception that the top cell in Latest1 is this weeks result and the top cell in Previous1 is the 1st ball picked last week. Being side by side so that I can tell the difference between this weeks pick and last weeks pick at a glance.

There are six groups of data comprised of two colums each - Latest1 & Previous1 (together), Latest2 & Previous2 (together.).. to Latest6 & Previous6 (together) and so on.. I have kept the 1st, 2nd ... 6th balls picked  ranges together for easy analysis.

Assuming that I am looking for the number "1" I presume if I can now loop the above formula in some way, I would then be able to find the address of the next "1" and then count the cells in between, assuming all contain numbers - store the result of the count in an individual cell for later analysis, save the address of the last "1" and repeat the process until I run out of "1's" or hit an empty cell in this range. Each count being saved in an individual cell - be it an array or Named range.

I will then use these Data Ranges to analyse the various forms of data collected  in such a way as to include the uses of Bedford's Law and others in the hope of reducing the odds in my favour.

In Worksheet 2 you have  graph1 of the various result from Ball pick No.1 (Latest1 & Previous1), in worksheet to a graph2 of the results of ball pick no.2 and so on to worksheet 6. I will also being doing other analysis with the assistance of the Graph's on these worksheets.

There are other column Named Ranges attached to the 6 sets of data but to mentioned these, I feel, would only confuse the issue. That's if I haven't already done so - by my poor explination.

I am hope this helps but if I can help further I will do my best.

Bob - May I also take this opportunity to thank you for your reply.

Filed under: , ,
• Post Points: 5

Re: Count the number of occupied cells in a Named Range between two No's. the same and repeat down the range until a empty cell is found

 I have been working on this during the night and have so far come up with this. Being a relative beginner it probably isn't the bestsolution but here goes:- To find the first No in the 52 cell range named Latest2 (K6:K57)I have written this:- =ADDRESS(ROW(Latest2)+MATCH(K77,Latest2,0)-1,COLUMN(Latest2),4,1) which I placed in C75 just to test it. To find the second No in the range I used :- =ADDRESS(ROW(Latest2)+MATCH(K77,Latest2,1)-1,COLUMN(Latest2),4,1) which I placed in C76. This gave me the correct relative addresses of: K6 & K11. I then entered the following formula to count the numbers in between the two addreses:- =COUNT(C75:C76) However =Count read c75 as C75 and C76 as C76 and returned a value  of 0. I would very much appreciate it if someone would kindly tell me where I am going wrong.
Filed under: , ,
• Post Points: 21