Excel help needed

[bloubul]

Hi PAB

Please help me here. In OS XP Pro and in Excel I had two macro's for my powerball's powerball. The macro looks at ColumnB1 till where it ends and allocate the powerball in G1 till where Column B stops from 1 to 20 in equal spaces. I also have a nother macro that allocate the 20 numbers at random also in G1 till Column B1: till it stops. But now in Windows 7 Pro in excel it tells me :Ambiguous name detected: pb. In Xp now problem, windows 7 Pro major problems.

Please help.
BlouBul

 

[PAB]

Hi BlouBul,

I will start a new thread for this as it is totally off topic and it will confuse other members and guests who are following the original request of Repeat Misses Of The 5 Decades.
The new thread is called Powerball Macro Not Working In Windows 7 But Did In XP Professional in the Anything Goes Forum.

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]

Hi Frank,

I have just been looking at your WorkBook, very nice indeed.
In your WorkBook I have noticed that you have used Named Ranges, Conditional Formatting & Filters etc.
There are a couple of things I am unsure about.
(1) In cells D4:H9 you have 1's in them, what are these for please?
(2) In cells D11:H11266 there are some cells with ZERO's in and some cells that are BLANK.
Did you know that if you did not want to use Conditional Formatting with the Font set to White to hide the ZERO's you could just Number Custom Format the cells as 0;0;;@ or 0;-0;;@.

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.

 

[Frank]

Hi Frank,

I have just been looking at your WorkBook, very nice indeed.
In your WorkBook I have noticed that you have used Named Ranges, Conditional Formatting & Filters etc.
There are a couple of things I am unsure about.
(1) In cells D4:H9 you have 1's in them, what are these for please?
(2) In cells D11:H11266 there are some cells with ZERO's in and some cells that are BLANK.
Did you know that if you did not want to use Conditional Formatting with the Font set to White to hide the ZERO's you could just Number Custom Format the cells as 0;0;;@ or 0;-0;;@.

Regards,
PAB

Okay, Question 2 first,
In cells D11:H11266 there are some cells with ZERO's in and some cells that are BLANK. Presumably you wondered why ?

Simply to comply with Icewynds original convention where he decided to signify a missed decade with a zero. He could have chosen to use 1's or any other symbol, but he chose zeros. So I used zeros to signify a missed decade and empty text "" (not blank) for decades present in the draw on that row. I could have used anything, but that was my choice. The important thing was a flag was needed so that the macro could tell which was which.

Question(1) In cells D4:H9 you have 1's in them, what are these for please?

You might equally have asked why the tables start on row 11 rather than row2 ? The answer is that the macro selects a 'flag' taken from columns D to H and looks back through the previous 6 flags in that column to determine whether it is consecutive or not. As I chose to work from the top down, this meant that when checking draw 1 it checked cells higher up the table to look for zeros. It initally gave error messages when it tried to read the labels (which I then moved up out of range). Then it became clear that if the very top draw happenned to have a zero flag, the consecutive checker (looking up) would see the empty cells in black as zeroes and incorrectly assign a 7 in the right hand miss counter table.(Try it and see). For expedience I assigned those cells a non blank non zero value of 1. Yes I could have used a space instead or a single " mark, but I used a 1, it got the job done.

I did not know about that custom format for hiding zeros, good tip that one !
Overall, the main thing was the spreadsheet got the job done and answered the question ( or did it ? you don't say), I think which is all I set out to do..

 

[PAB]

Thanks for the reply and explanations Frank, it makes sense now, I appreciate it.

Overall, the main thing was the spreadsheet got the job done and answered the question ( or did it ? you don't say), I think which is all I set out to do..

Apologies Frank, I omitted to say yes, it appears to have produced the requested results.

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.

 

[Frank]

Thanks for your comments, PAB I hope those who downloaded it found it useful.

 

[PAB]

Hi Frank,

You are more than welcome, and thank you for your time and effort on this.

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]

Hi Frank,

You are more than welcome, and thank you for your time and effort on this.
At the end of the day, we are ALL trying to catch that ever elusive JACKPOT.

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.

 

[emilyng]

Hi Frank

I have the same problem, could you share with me the spreadsheet as well?

Thanks

 

[emilyng]

Hi Frank,

Possible to share with me the excel file?

 

[Frank]

Emilyng, you have not participated in this discussion at all, and this topic appears to be dead now, even the originator has not responded. I am now only sharing it with people who have demonstrated that they are involved this this specific aspect of lottery analysis and with people experienced enough with Excel macro enabled spreadsheets to be able to effectively use it and report back their findings. So far I have not seen enough evidence that this sheet would be right for you, hopefully that may change in the near future, but for now the answer is no.

Regards,

Frank

 

[Icewynd]

this topic appears to be dead now, even the originator has not responded.

Sorry about that Frank. Rellies moved in for a few months and totally
#%@$'d up my routine!

I have been reading through this thread and it seems that you have nailed the analysis. Obviously I couldn't download the spreadsheet at this late date, but I reviewed the table you posted and it concurs with my analysis of 100 games (manually counted).

The purpose of my original query was to determine the probability of a decade missing in the next draw when it was missing in the most recent draw. That is, if the most recent results are 10-15-26-30-42-49, what is the probability that D1 (0-9) will be missing again in the next draw? From your table of the German lotto results, I estimate that it is about 25%, i.e. 25% of single misses will become doubles and 25% of doubles will become three-in-a-row. Let me know if this is not a fair interpretation of your data.

BTW, Canada's 6/49 Lotto had an example of an eight-in-a-row miss for D1 (draws 2879 - 2886).

Thanks for all your hard work.
I will contact you via your website regarding the spreadsheet.

Icewynd

 

[Frank]

Hi Icewynd, I have sent you a copy of the latest version of the spreadsheet, it does only go up to 7 misses despite the possibility of 8 as you say. Let me know if you have any questions.

I regard the probability of a double decade miss as absolute and according to my spreadsheet for the spanish Lottery it is 3.43% regardless of any other events. The ocurrence of single decade misses is not positionally related to the positions of double misses. The only thing you can say is that there are about four times as many single misses as double misses ! The scattering is random. So I disagree with your interpretation of my data. I can see why you think that though, due to the 4 to 1 difference.