Excel help needed

I

Icewynd

Member
Can anyone help me out with a method to count repeaters on subsequent draws? Eg. Hit on row 3,4,5 in the same column = a triple.

I suppose I could run moving averages for 2, 3, 4, 5 games and pick out those that equal zero (I use zero to indicate a hit), but there must be a more elegant solution than that.

All suggestions appreciated :wavey:
 
Last edited:
bloubul

bloubul

Member
Hi Icewynd

Try this formula, I'm not a guru with excel, but I can help myself.
Your Lotto numbers must start on C4, D4, E4, F4, G4, H4. I do not enclude the bonus ball. (6/49 or 5/45)
Copy this formula in column in:
1. Column K5 =IF(OR(C5=$C4,C5=$D4,C5=$E4,C5=$F4,C5=$G4,C5=$H4),C5,"")
2. Column L5 =IF(OR(D5=$C4,D5=$D4,D5=$E4,D5=$F4,D5=$G4,D5=$H4),D5,"")
3. Column M5 =IF(OR(E5=$C4,E5=$D4,E5=$E4,E5=$F4,E5=$G4,E5=$H4),E5,"")
4. Column N5 =IF(OR(F5=$C4,F5=$D4,F5=$E4,F5=$F4,F5=$G4,F5=$H4),F5,"")
5. Column O5 =IF(OR(G5=$C4,G5=$D4,G5=$E4,G5=$F4,G5=$G4,G5=$H4),G5,"")
6. Column P5=IF(OR(H5=$C4,H5=$D4,H5=$E4,H5=$F4,H5=$G4,H5=$H4),H5,"")
Now copy it down all the way as far as needed.

If it is what you want, don't thank me, thank PAB for this formula :thumb:

BlouBul :cool:
 
I

Icewynd

Member
OK, I tried this out and it is not what I need, but I see that my original request was not very clear.

I want to count the repeat misses of the 5 Decades. So, I have 5 columns C, D, E, F, G (D1, D2, D3, D4, D5) which contain the number of hits from each decade. What I would like to know is, for each decade how many times do the decades miss in a row? Eg. how many doubles, triples, quads, quints (haven't noticed any 6-in-a-row misses for any decade). What I want to know is, for each decade, if I am sitting on a double miss what are the chances of a 3-peat?

So, anyone? I'll buy ya a virtual beer :beer:
 
P

PAB

Member
Hi Icewynd,

I know it has been a while since you posted this request but are you still after a resolution?
If so, could you please post a bit more detail and I will have a look at it for you.

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
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.
 
bloubul

bloubul

Member
Hi PAB

It's good to see you back on the BB. Do you still have contact with GillesD...... He does not respond on any of my mails........

BlouBul:cool:
 
P

PAB

Member
Hi BlouBul,

It is great to be back.
No, I am not in contact with GillesD.
I am going to start a new thread for this as it has nothing to do with this threads topic.

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
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.
 
W

Winalot2007

Member
Repeat Numbers

=MIN(IF(COUNTIF(E20:I20,E21)>0,1)+IF(COUNTIF(E20:I20,F21)>0,1)+IF(COUNTIF(E20:I20,G21)>0,1)+IF(COUNTIF(E20:I20,H21)>0,1)+IF(COUNTIF(E20:I20,I21)>0,1),IF(COUNTIF(E21:J21,E20)>0,1)+IF(COUNTIF(E21:J21,F20)>0,1)+IF(COUNTIF(E21:J21,G20)>0,1)+IF(COUNTIF(E21:J21,H20)>0,1)+IF(COUNTIF(G21:I21,I20)>0,1))

All my current drawings will always be in E20 through I20 for a 5 Digit Game.
I keep adding a new line 20 when a new drawing occurs.

I know this probably is not what you wanted but thought it might help.
 
Frank

Frank

Member
Icewynd said:
OK, I tried this out and it is not what I need, but I see that my original request was not very clear.

I want to count the repeat misses of the 5 Decades. So, I have 5 columns C, D, E, F, G (D1, D2, D3, D4, D5) which contain the number of hits from each decade. What I would like to know is, for each decade how many times do the decades miss in a row? Eg. how many doubles, triples, quads, quints (haven't noticed any 6-in-a-row misses for any decade). What I want to know is, for each decade, if I am sitting on a double miss what are the chances of a 3-peat?

So, anyone? I'll buy ya a virtual beer :beer:
Click to expand...


I've just had a look at this, I hope I understood your question correctly. I can't do it with formulas so I did it with a macro. Its no good me posting the macro as it needs to run in its own context on the sheet since it references named ranges to gather and read out the information. What I did find is that the (overall) statistics of decade misses are the same for all 6/49 lotteries. I ran the macro for the UK lotto 1678 draws, and then replaced those results with sets of 1678 random results. The probabilities are pretty similar for all of them.



The image shows the table of results

http://i40.tinypic.com/15wmdco.gif

So looking at the first decade (0-9) over 1678 draws we had 258 single misses which was about 15% of 1678. The other decades were similar values and taking the average gave an occurence of 14.2 % and is about a 1 in 6 chance of a single decade skip happening in isolation.
Taking the averages of all the counts in the decades along the rows :-

Double misses were 45 equating to 3% of all draws and odds of 1 in 32.

Triplets were 0.8 % and the odds against this were around 116 to 1

Quads were 0.23% and 440 to 1 against.

Quins less than 0.1% and about 1200 to 1.

Sextups, yes there were some, about 0.01% more than 8000 to 1 of it happening.

The overall odds of ANY repeat miss of any type (single, double etc) in any of the 5 decade columns are 1 in 4.4.


So, you have to remember though, that as you progress through the draws and 'know' how many decade misses there have been in the current 'run' and therefore mentally work out what the odds are of there being another miss (from the above) .......................

That the lottery has no memory, doesn't know what you 'know' and the true odds of there being a miss in that column (or any other) regardless of how long the run is in the next draw are - One in 4.4 ! :eek:

I'd share the spreadsheet if only I could find a way of anonymously (without disclosing e-mail addresses) uploading it. Then you could try it for yourself.
 
savagegoose

savagegoose

Member
i know it wont help with a macro, but google have a shared spread sheet online.
if the creator ticks the appropriate boxes in the share section , then anyone can edit the sheet.
so if someone has a prob. they can post it, share, and next person can copy to the 2nd page, so as not to mess up the 1st page, and show how they did it.

ill set one up and see if it works like i advertised
https://docs.google.com/spreadsheet/ccc?key=0AlwFpybp6ZlndDVfZl9sWjNrYlU2REVEYldSMVlLRkE

i didnt mean to interrupt, just i figure this is some way to show exactly what the problem they want sorted in , and people who can fix it can use same program as the requester.
 
Frank

Frank

Member
Thanks Savagegoose, yes I know all about Google docs and I have used them on this site, but they reveal my e-mail address and I don't want that. Sky drive is the same.

So I've tried mediafire and it appears to just provide a link which is all I require. This upoaded file, I think will be temporarily hosted and then be deleted. Fine. I've zipped it up as its a 5Mb Excel doc, so you'll be downloading a 949K file, missed decade repeat counter.zip.

http://www.mediafire.com/file/opl7pq2d2hcafic

I've tested this Excel doc in Excel 2000- and Excel 2010 and I can't see any compatibilty problems. Naturally as it contains a macro you will get the usual warning.

I suggest you look at the notes page first, to see what it requires to work, and what it is actually doing. Whilst I've specified that your results need to be oldest at the top, it doesn't really matter for large lists, the results in my tests come up the same either way provided you use exactly those columns. There is a filter to enable you to find when a given multiple miss occurs.
The sheet is protected but not passworded, so you could remove protection at your own risk if you want to do so. The macro may malfunction if you change the structure too much.

Have a play and see what you get ! :)
 
Frank

Frank

Member
Well I took advantage of Bloubuls German results upload (Thank you) courtesy of PMB's request and ran them through my speadsheet. I noticed that since the macro is only programmed to look for up to 6 decade misses it missed the fact that there is an instance of 7 decade misses in this 3192 draw set. So the version I shared with you found three 6's instead of two 6's and a 7. I've since modified my copy to cope with 7 misses and up to 10300 draws.

Because it was a much larger set of results the results are a bit different (see image) to reflect the greater accuracy of a bigger sample set.

http://i40.tinypic.com/wm13xx.gif

There isn't really enough data even in 3000 draws for accurately finding the odds of the 4's 6's 7's misses as they are so infrequent.

I did do a trial run with 10000 random draws and got:-

odds against single missed decade 1 in 6.324
odds against double missed decade 1 in 29
odds against triple missed decade 1 in 119
odds against quad missed decade 1 in 511
odds against quin missed decade 1 in 1566
odds against any type of miss in any decade 1 in 4.53

These could be way out due to rarity ...

odds against sextup missed decade 1 in 7386
odds against heptup missed decade 1 in 50000.

I see that about 9 people have downloaded the sheet and its all gone quiet. Some feedback would be appreciated, how did you go on ? Thank you.
 
P

PAB

Member
Hi Frank,

I tried downloading ...

http://www.mediafire.com/file/opl7pq2d2hcafic
Click to expand...
... but it is telling me that it is password protected.
I would be interested in looking at the file so if you could either let me know the password or if that is not possible perhaps you could upload the file again without password protecting it.
Many thanks.

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
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

Frank

Member
Hi PAB,

Thank you for responding, The file was available without a password for 4 days. After 9 people had downloaded it and there was no response to my request for feedback I was quite disappointed. I like to think that the people who are downloading spreadsheets do so because they are interested in the topic or in Excel and would be good enough to acknowlege that they had downloaded it and let me know if it was of help to them and share the results they got.
When 17 people had downloaded it and the deafening silence continued, I decided that I would password protect it.
That way only people who have a genuine interest in the topic and who are prepared to make an effort in sharing their findings would make themselves known to me.

PAB I can tell from reading your contributions that you are an experienced Excel user and would be happy to make my updated version of the file available to you. I suspect since you are in the UK we may have met in UK forums and you will know how to contact me via my Lotterygen site. Drop me an e-mail and I will share it with you.
:liplick:
 
P

PAB

Member
Hi Frank,

Thanks for the reply and for agreeing to send me the Excel file.
I think GillesD, myself and you used to visit the Doctor's together on a regular basis.
I hope you are keeping well.
I have sent you an email as requested.

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
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.
 
P

PAB

Member
Thanks Frank, I have sent you an email.

Regards,
PAB
:wavey:

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
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.
 
You must log in or register to reply here.

Sidebar

Top