# Starting from the Beginning

#### Alexafjb

##### Member
Hello to All, Especially to the: "Masters of Excel Slash VBA Programmer Guru’s"

I’ve started this thread to get feedback from you folks who know your stuff!
My intent is mainly to receive guidance in my endeavor to create my own set of tools, for the lotto I play. i.e. 6/49, 7/49 style lotto.
Now, I’m not asking for the folks who know their stuff to build it for me… that would be just wrong, not to mention, no fun at all
for me, and anyone else that wish’s to expand their skill set with Excel.
As for my skill with Excel? Well, let’s just say I know enough to get into trouble!
Back in the late 90’s I piddled around with BASIC and then VB.
Wrote a few macro’s within early versions of Excel. My current version of Excel is 2007.

I've a number of questions.
First off.
The Data Base (D.B.)of past winning numbers, that I wish to create (but not set in stone yet) will be a row from 1 to 49 with winning numbers marked as the winning integers,
followed by the, combinations CSN(Lex), win combo Sum, Odd, Even, then Number of Winning Prime Numbers.
My ultimate goal here is to create a Macro/Module, that I can make and it will search for past winning combinations of patterns. (Nothing New Here everyone is trying to do it!)
What it gets down to though, is how the workbook is setup.
I wish to link worksheets, use a naming convention for different parts of the D.B. I also feel that this way is best for add future analytical module’s.

What do you all think? What ways would you folks set up a similar D.B.? Is it feasible?
I see it, in my mind’s eye, maybe I need to elaborate more on my concept of theory?
As everyone knows, this topic alone could open up a HUGE can of UGLY!
Serf ice it to say before anyone else does!
All numbers have an equal chance, Mathematics Rules the Universe, Time is infinite & We are not!, and Opinions are like ___holes everyone has one.
;-)
I'm trying to avoid future errors, that is why I’m asking the people who have seen the mistakes already.

Looking forward! THX All
Alex.

#### Icewynd

##### Member
Alex,

I would suggest that you go to this thread: http://www.lottoforums.com/lottery/lotto-tips-strategies/12799-vtracs-6-49-a-12.html

Although this exercise uses only 24 'Vtracs' the spreadsheet could be easily modified to include all 49 lotto numbers.

Let me know what you think.

#### PAB

##### Member
Hi Alex,

Alexafjb said:
...will be a row from 1 to 49 with winning numbers marked as the winning integers...
Do you mean the 6 numbers drawn will be from 1 to 49 with each draw being in its own row?

I see you have listed five analysis criteria that you would like to start off with. As you can imagine, there a numerous others that can also be used.

You say your goal is to create a Macro/Module that can search for past winning combinations of patterns. Depending on what you mean exactly, I would think that Excel formulas could be created to incorporate most of what you want to do.

You basically want to know what is the best way to setup your initial SpreadSheet. This is very much a personal thing as everybody wants their own data setup in a way that is suitable for them.

You say you want to link WorkSheets using named ranges I assume for the different parts of the SpreadSheet. Again, this is very much a personal thing and I personally very rarely use named ranges.

I would assume that every Lotto player has a Database of past results and various analysis criteria setup, so yes, it is feasible.

I myself have an Excel WorkBook Database that produces literally hundreds of different analysis.

Anyway, I have put a SpreadSheet together for you based on the five criteria you initially stated, which I would be happy to upload for you to look at. This by no means implies that this is the best way to do it or it is the way that you should do it, it is simply to give you an idea of how to kick yours off with.

I personally use MediaFire for uploading files etc, it is Cloud based, but best of all it is FREE. If you are happy to register then go ahead and let me know when you have done so and I will upload the SpreadSheet for you to look at.

I hope this post has at least been helpful in some way?

BTW, I totally agree with Icewynd's suggestion, if you download the file he mentioned it will give you a good idea of what sort of analysis there is available. Icewynd and I have been working on that thread for a while now and it has evolved into a very nice analytical exercise to say the least.

Regards,
PAB

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

#### Alexafjb

##### Member
Hello, Icewynd & PAB

I appreciate it very much!
Gentlemen, I will have to join media fire to take advantage of your gracious offers! Thank You both!
Looking forward to studying it.
You both have no problem with others using your hard work?

Cheers.
Alex.

#### PAB

##### Member
Alexafjb said:
You're welcome Alex.

Alexafjb said:
You both have no problem with others using your hard work?
This forum is all about learning and exchanging ideas and opinions, we all have something to learn regardless of what level we are at.

Regards,
PAB

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

#### Alexafjb

##### Member
PAB

I personally use MediaFire for uploading files etc, it is Cloud based, but best of all it is FREE. If you are happy to register then go ahead and let me know when you have done so and I will upload the SpreadSheet for you to look at.
I’ve gone ahead and joined Mediafire. I’ve DWNloaded Icewynd’s suggestion. Going to have a look at that one soon. I’ve a few things to take care of today so won’t get to it till early in the morning.

Cheers.
Alex.

#### PAB

##### Member
No worries Alex .

If you get some spare time it might be worth you reading through that whole thread, yes I know it is fourteen pages long but I think the exercise would be worth while, it will give you a good idea of what is achievable. Icewynd and myself have put quite a few hours into that particular project that will become apparant if you manage to review the whole thread.

Once you have looked through the file you have just downloaded I will upload the SpreadSheet I have put together for you with the criteria you stated for you to look at.

Regards,
PAB

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

#### PAB

##### Member
Hi Alex,

I've noticed you are online at the moment.
Here is the file I mentioned above.
I will leave it up for an hour to give you time to download it.

http://www.mediafire.com/?k9ef4qj9r7krnd5

Regards,
PAB

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

#### Alexafjb

##### Member
THX PAB

I've got the file.

Cheers.
Alex.

#### PAB

##### Member
Hi Alex,

I forgot to mention that there are hidden formulas in cells A1:AD1, well, when I say hidden formulas I mean the font for them is white so they don't actually show up, just change the font to black to see them.
Please let me know what you think!

Regards,
PAB

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

#### PAB

##### Member
Hi Alex,

As I said in my previous post,

PAB said:
Anyway, I have put a SpreadSheet together for you based on the five criteria you initially stated.
This by no means implies that this is the best way to do it or it is the way that you should do it, it is simply to give you an idea of how to kick yours off with.
It is just simply an example.
I put this together for you using snippets from my own Excel DataBase that I have setup and use.

Please let me know what you think!

Regards,
PAB

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

#### kurtie

##### Member
PAB said:
Hi Alex,

I've noticed you are online at the moment.
Here is the file I mentioned above.
I will leave it up for an hour to give you time to download it.

http://www.mediafire.com/?k9ef4qj9r7krnd5

Regards,
PAB

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

can i use this also for 6/45

please can i downl somewhere this file?

rgds,
kurtie

#### Alexafjb

##### Member
Hi PAB

Had a look at the file this morning, and re visited it just now

It is just simply an example.
I put this together for you using snippets from my own Excel DataBase that I have setup and use.
Please let me know what you think!
Would I be right in my assumptions that, it’s all formulas, no Macros? (Just making sure that I got the right file from you!)

There is no relativity between certain formulas? (Was kind of rubbing my eyes earlier, makes much more sense now!)
Lots that I could use, that’s for sure!
I also looked at Icewynd and your endeavor. Hell of a lot of work there!!!! Not sure if I can grasp the theory of Vtrac’s just yet.

Cheers.
Alex.

#### PAB

##### Member
Hi Alex,

Alexafjb said:
Would I be right in my assumptions that, it’s all formulas, no Macros? (Just making sure that I got the right file from you!)
Yes, that is right.
It is just to give you an idea and maybe a start with setting up your own DataBase.
If you were to do this using a Macro(s) it would take some serious programming code to achieve it, especially when you are talking about adding extra scenarios and conditions at a later date.
I hope you found it useful at least as a start?

Regards,
PAB

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

#### Alexafjb

##### Member
Data Base Example

Hey PAB

In the example D.B. You have a "Warning" and "X" error checking routine?
I'm not sure what it's checking for?
I know its for the D.B. section "Numbers In Sequence Drawn" .
( By the way in any Lotto in Canada, it's difficult if not impossable to find out, (with out watching the draw happen) what sequence the numbers are drawen in! )
Could you give to me a run down of what it's checking?
THX.

Cheers.
Alex.

#### Alexafjb

##### Member
Hi PAB

Smiles, I'm seeing formula references and trying to logically walk thru. But it's difficult! I guess a cut & paste will do that! It's giving me a few ideas, for sure just have to work it out, you know trial and error in my Woorkbook. THX though!

Cheers.
Alex.

#### PAB

##### Member
Hi Alex,

Alexafjb said:
In the example D.B. You have a "Warning" and "X" error checking routine?
I'm not sure what it's checking for?
I know its for the D.B. section "Numbers In Sequence Drawn".
I will do this in stages:-

(1) Cell O2 checks that the Total Sum of Numbers In Sequence Drawn are the same as the Total Sum of Numbers Sorted Numerically. Just in case I had entered a number incorrectly.

(2) Cell U2 is the same as above but for the Bonus Ball.

(3) Cell W2 counts the number of formula entries for the CSN numbers against the total number of draws to date to make sure there is not a formula value missing.

(4) Cell U2 counts the number of formula entries for the Total Sum Of Combination numbers against the total number of draws to date to make sure there is not a formula value missing.

(5) Cell AA2 checks that the Total Sum for the Total ODD & EVEN numbers is the same as the Total Sum of the balls drawn to date TIMES 6.

(6) Cell AD2 counts the number of formula entries for the Total Prime Numbers numbers against the total number of draws to date to make sure there is not a formula value missing.

Alexafjb said:
( By the way, in any Lotto in Canada, it's difficult if not impossable to find out, ( with out watching the draw happen ) what sequence the numbers are drawn in! )
OK, but as I said before this was just snippets of my DataBase to give you an idea.

I hope this helps!

Regards,
PAB

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

#### Alexafjb

##### Member
Hey PAB Good Morning

Fabulous!

Thx, My brain gets in knot's at times.
After reading your last post "explaining" it! Not sure why I did not figure it out on my own? It's been a while since I sat down and worked with Excel! Best brush up!

Be patient it won't be a very steep re-learning curve!

Cheers.
Alex.