Excel help please

[jol]

Hi
I am after importing UK lottery data from inception to present day. I use Mersey world for that but if you know of a better place then thats OK.
I would then like to look at each row of data and count hits per row of both the last draw and indiviual numbers that i wish to find.
It would be nice to have the data (hits per row) next to each other so I can compare.
My understanding of Excel is next to non exsistant so I will need loads of help.
Thanks in advance.

Jol

 

[PAB]

Hi Jol,

I am after importing UK lottery data from inception to present day. I use Mersey world for that but if you know of a better place then thats OK.
I would then like to look at each row of data and count hits per row of both the last draw and indiviual numbers that i wish to find.
It would be nice to have the data (hits per row) next to each other so I can compare.
My understanding of Excel is next to non exsistant so I will need loads of help.

(1) Are you saying that you don't at present have the data and that you intend to import from MerseyWorld?

(2) Is it for the UK Main 649 Lotto, because if it is I can upload ALL the draws to date for you in an Excel file to MediaFire.

(3) When you say...

"I would then like to look at each row of data and count hits per row of both the last draw and indiviual numbers that i wish to find."

... do you mean that you want to count the number of numbers that hit in the current draw that were ALSO drawn in the last draw, and do you also mean that you have a set of 6 STATIC numbers that you also want to count how many of those have been drawn in each draw?

If you can answer the above 3 then I might be able to sort something out for you.

Also, it would be a good idea if you were to put your location in your profile, that way everybody can see where you are from and people from the UK for example are more likely to reply rather than not knowing exactly where you are from or what Lotto you are playing.

I hope this helps!

Regards,
PAB


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

 

[jol]

PAB
Profile updated thanks for the heads up.
I use covermaster and load the full history. If I then press find and type upto 7 numbers it will highlight all those numbers in black this is great but unless I screen dump some 20 pages I cant save that view. I would like to do somthing similar with Excel but using 1 to say 30 numbers.
I don`t know whats possible so would leave it up to you how it looks and works.
I hope that explains slightly better than the original post.
Thanks in advance for your help

 

[PAB]

Hi Jol,

Profile updated thanks for the heads up.

You're welcome !

I use covermaster and load the full history. If I then press find and type upto 7 numbers it will highlight all those numbers in black this is great but unless I screen dump some 20 pages I cant save that view. I would like to do somthing similar with Excel but using 1 to say 30 numbers.

Covermaster isn't really intended for that sort of calculation but I think I understand what you are after. The results that you get from using find and are highlighted in black are the 7 STATIC numbers that you want to find out how many times they appear in each combination, is that right???

I still don't understand what you mean when you say "1 to say 30 numbers."

I think it will still help me to understand better if you could answer these questions please....

(1) Are you saying that you don't at present have the data and that you intend to import from MerseyWorld?

(2) Is it for the UK Main 649 Lotto, because if it is I can upload ALL the draws to date for you in an Excel file to MediaFire.

(3) When you say...

"I would then like to look at each row of data and count hits per row of both the last draw and indiviual numbers that i wish to find."

... do you mean that you want to count the number of numbers that hit in the current draw that were ALSO drawn in the last draw, and do you also mean that you have a set of 6 STATIC numbers that you also want to count how many of those have been drawn in each draw?

If you can answer the above 3 then I might be able to sort something out for you.

Once I am clear on what it is EXACTLY that you want I can put something together for you.

Regards,
PAB


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

 

[jol]

PAB
You have the patience of a saint Sir.
1 to 30 = I would like to check the current draw numbers againt the previous history and find the hits per row. I would also like to do that with more numbers say upto 30.
(1) I have the data from start to last draw and can import from MW.
(2) Yes from Main Lotto
(3) Now you have me and I understand what you must be going through trying to understand my request LOL. CoverMaster explanation is probably the best. I want to understand the current trend therefore use the last 30ish numers drawn and look back at the history to find the nearest match, like I said I can do this but only with 7 numbers max. I need to be able to do the same thing with more numbers. I dont need excel to tell me the Current numbers I can find those myself.
Thank you for your time.
Jol

 

[PAB]

Hi Jol,

Let's take this in stages. Firstly...

1 to 30 = I would like to check the current draw numbers againt the previous history and find the hits per row. I would also like to do that with more numbers say upto 30.
(1) I have the data from start to last draw and can import from MW.
(2) Yes from Main Lotto

Do you want me to upload you a file that contains ALL the UK Main 649 numbers to date along with the total numbers for each draw that have been drawn in the previous draw, along with a section where you enter 6 numbers of your choice and it tells you how many of those 6 numbers have been drawn in each draw? I think this answers the first part!
With the file, all you will need to do is to add the 6 numbers plus the Bonus ball for each NEW draw!

Secondly...

(3) Now you have me and I understand what you must be going through trying to understand my request LOL. CoverMaster explanation is probably the best. I want to understand the current trend therefore use the last 30ish numers drawn and look back at the history to find the nearest match, like I said I can do this but only with 7 numbers max. I need to be able to do the same thing with more numbers. I dont need Excel to tell me the current numbers I can find those myself.

This will be more complicated to accomplish with Excel formulas if I understand you correctly.
Let's deal with the first bit first and get you up and running with something as a start point. Then maybe a more detailed explanation for the second part will be needed before that part is put together.

I did actually put something together last night along the lines of what I interpreted your request to be.

Regards,
PAB


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

 

[jol]

PAB
Yes please a upload would be great.
I agree I have signed up for mediafire today.
If nothing less it will give me chance to show you screen dumps etc of what I am after.
Again thanks for your help.
Buy ya a pint next time I am down the smoke.
I used to live in St Albans my brother still lives down that way but has moved to Hemel (Crazy roundabouts)

 

[PAB]

Hi Jol,

I don't know how good you are with Excel, but what you could do if you so wished is to hide the 0's in cells M11:N3009 either by extending the current formulas or by using a Custom Format.
You could also Conditionally Format cells E10:J3009 to show up as a RED background ONLY for those numbers that MATCH the numbers you enter into cells E3:J3, that will give you are very good visual effect of what numbers MATCHED in EACH of the draws.
The numbers you enter into cells E3:J3 can be ANY numbers you want.

The above is just a thought!

Here is the file...

http://www.mediafire.com/download/tu8ufuo4z86mg5i/MediaFire_-_Jol_-_UK_Main_649_Lotto_v1.0.xlsm

Please let me know what you think!

Regards,
PAB


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

 

[jol]

PAB.
I found the file and saved. Thank you.

 

[PAB]

Hi Jol,

I found the file and saved it. Thank you.

You're welcome!

At least it's a starting point and you're up and running.
You could now do what I suggested in my previous post to make it more visually effective.
I hope this helps!

Regards,
PAB


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

 

[PAB]

Hi Jol,

How's it going, was the file any good for what you are after?
Did you manage to do the updates OK?

Regards,
PAB


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

 

[jol]

PAB
Having a bit of bother have excel 2000 at home and I am off work for a couple of weeks so can`t get to my work Pc. I tried the net answers to running a macro but to no avail.
I have a new lap top comming soon so will run it on that one when it arrives.
Thanks again for you help.

Can I ask what do you think about using historical data to select numbers. I am finding that because the rows contain past numbers you only find a portion of the numbers needed. Think of the data snaking its way down the rows, depending where you take your data from it only shows 3 or 4 numbers i.e first to middle column data or middle to top data unless your lucky. ??
Much still to do I will have a bash while I am off.

 

[PAB]

Hi Jol,

Having a bit of bother have excel 2000 at home and I am off work for a couple of weeks so can't get to my work PC. I tried the net answers to running a macro but to no avail.

What Macro?

The file I uploaded for you is purely a formula based SpreadSheet with NO Macro's.
Perhaps you could post the Macro you are using for me to look at and see if I can adapt it to your needs please!

Can I ask, what do you think about using historical data to select numbers. I am finding that because the rows contain past numbers you only find a portion of the numbers needed. Think of the data snaking its way down the rows, depending where you take your data from it only shows 3 or 4 numbers i.e first to middle column data or middle to top data unless your lucky.

Well, there is no easy answer to this.

If you don't use historical data to pick your numbers then there is no need to keep a SpreadSheet or any statistical analysis at all, or even the numbers that are drawn. You can then just pick your numbers by any method that you feel comfortable with!

In theory, using statistical analysis gives you a slight edge with respect to seeing trends of what has been, or what is to come.
I personally think that using filter parameters related to what the Minimum & Maximum limits for the entirety of the numbers drawn so far for example, less a reduction of whatever I class as well outside the norm is advantageous when choosing which numbers to play.

Although, as you well know, every combination has as much chance of being drawn as any other.

I hope this helps!

Regards,
PAB


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

 

[PAB]

Hi Jol,

As a follow up, I just did some investigating as to why the formulas in the SpreadSheet that I uploaded for you don't work. You said that you were using Excel 2000, and although this is quite an old version, the formulas I included in the SpreadSheet do actually work in Excel 2000, so I don't understand what the problem is!!!

Regards,
PAB


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

 

[jol]

Hi PAB.
Macro is my mistake because it would not open I presumed it was a macro file and went looking for a answer myself rather than bother you.
Here`s what happens I double click the file and Excel opens but then says it is "This file is not in a recognizable format" ?
PK!ô§Ë…
úÜ
[Content_Types].xml ¢Ø
(*¬”AnÂ0E÷•z‡ÈÛ*1tQU�¥Ë©ôÆž�Ƕ<††Ûw’”¨ š‚è&‘ÍÿÞŒ3šT¥Ž¶à±°&eÃdÀ"0Ҫ¬Rö±x‰Y„A%´5�² ›ŒooF‹�Œ¨Ú`ÊòÜç(s(&Ö�¡/™õ¥tô+î„\‹ðûÁà�Kk˜‡Zƒ�GoÀ
Is the first line of the text if I force Excel to open it.
New Laptop will be here tomorrow has Windows 8 and new office or something like that so will try again then.
Thanks for the reply on history how do I learn more on filters because when I have done them in the past they remove numbers I want or still leave hundreds of thousands of possibilities.

 

[PAB]

Hi Jol,

Here`s what happens I double click the file and Excel opens but then says it is "This file is not in a recognizable format" ?
PK!ô§Ë…
úÜ
[Content_Types].xml ¢Ø
(*¬”AnÂ0E÷•z‡ÈÛ*1tQU�¥Ë©ôÆž�Ƕ<††Ûw’”¨ š‚è&‘ÍÿÞŒ3šT¥Ž¶à±°&eÃdÀ"0Ҫ¬Rö±x‰Y„A%´5�² ›ŒooF‹�Œ¨Ú`ÊòÜç(s(&Ö�¡/™õ¥tô+î„\‹ðûÁà�Kk˜‡Zƒ�GoÀ
Is the first line of the text if I force Excel to open it.

OK, I see what's wrong!
It's a compatability issue.
I have uploaded a new file for you in the format that Excel 2000 will recognise, so this file will work.

http://www.mediafire.com/download/vv62w3t62nzdetb/MediaFire_-_Jol_-_UK_Main_649_Lotto_(97-2003_WorkBook)_v1.0.xls

Thanks for the reply on history how do I learn more on filters because when I have done them in the past they remove numbers I want or still leave hundreds of thousands of possibilities.

Do a search of this forum because this topic has been covered on several occasions.
Here is a link about filters that I started.

http://www.lottoforums.com/lottery/lotto-tips-strategies/12903-filters-impact-associated-using-them.html

Copy & Paste the above link into your Browser.

Please let me know how you get on!

Regards,
PAB


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

 

[jol]

PAB
Thanks I have the file and it works great.
Your a mind reader just what I was doing LOL Loads of filters.

 

[PAB]

Hi Jol,

You're welcome!

I don't know how good you are with Excel, but what you could do if you so wished is to hide the 0's in cells M11:N3009 either by extending the current formulas or by using a Custom Format.
You could also Conditionally Format cells E10:J3009 to show up as a RED background ONLY for those numbers that MATCH the numbers you enter into cells E3:J3, that will give you are very good visual effect of what numbers MATCHED in EACH of the draws.
The numbers you enter into cells E3:J3 can be ANY numbers you want.

If you like the idea of the above and are unable to do this then I will amend and upload a new file for you with the above in.

Please let me know what you think.

Regards,
PAB


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

 

[jol]

HI PAB.
File is just what I wanted I will leave as it is.
I want now to copy all the data from "My Numbers" and paste into another worksheet.
Idea being I can then pick 6 other numbers and paste that next to the first column to compare.
What is the best way to do this. If I copy and paste the text does not copy over.
As I explained before Excel is not my strong point LOL.

 

[PAB]

Hi Jol,

I want now to copy all the data from "My Numbers" and paste into another worksheet.
Idea being I can then pick 6 other numbers and paste that next to the first column to compare.
What is the best way to do this. If I copy and paste the text does not copy over.

Firstly, I have uploaded a new file with the amendments for formatting on, I think you will agree that it gives a better visual effect for numbers that have been matched, as do the "Previous Numbers" & "My Numbers" without the clutter of the zero's.

http://www.mediafire.com/download/6om0olklwp0m7hb/MediaFire_-_Jol_-_UK_Main_649_Lotto_(Excel_97-2003_WorkBook)_v1.1.xls

Secondly, what range of cells are you wanting to copy? If you give me the range I will post back a way to do this, it is not complicated so don't worry.

Regards,
PAB


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