Good day everyone. I'm interested in a formula that can be used in excel that will count how many times a group of numbers came after a given number and list there frequency for a given draw duration. If I could also find out what number came after a said number not immediately after but say two draws or three draws after that would be even better. Thank you for your help and interest.
Excel announcer formula needed.
- Thread starter cdrake
- Start date
Frank
Member
Hi, its not 100% clear what you are looking for, there are several ways of interpreting what you asked for. You don't specify what you mean by "a group of numbers" and you don't specify how you will identify them.
The phrase "came after a given number" also is ambiguous, as a given number appears throughout lottery history, so how are you specifying where to start? This latter question sounds very much like the solution I have in the thread "markov chains", which (for a 649 lottery) counts up how many times ALL numbers appear after ALL numbers from a given point back in lottery history up to date. I presume your phrase "draw duration" would correspond to how far back in history the counting starts, but you don't say where it would end. In my version it always ends with the most recent draw.
My version also has a built in skip feature so you can count how many times ALL numbers followed ALL other numbers in every alternate draw, every 3rd draw, every nth draw if you like. The bad news is I haven't written any simple (or complex) formula(s) that will do this and be this flexible. Formulas may be possible if you are much more explicit about your requirements, no promises though.
I believe only a suite of macros can achieve this, and some brave people have followed my instructions to create a speadsheet that does exactly this. It makes a 49 x49 table (for a 49 ball lottery) listing the counts of Ball B following Ball A for the given number of draws back in time up to date. By all means look at the screenshots, and if its what you are looking for, you could reproduce it, if your skills include using named ranges and accessing the Visual Basic editor.
The phrase "came after a given number" also is ambiguous, as a given number appears throughout lottery history, so how are you specifying where to start? This latter question sounds very much like the solution I have in the thread "markov chains", which (for a 649 lottery) counts up how many times ALL numbers appear after ALL numbers from a given point back in lottery history up to date. I presume your phrase "draw duration" would correspond to how far back in history the counting starts, but you don't say where it would end. In my version it always ends with the most recent draw.
My version also has a built in skip feature so you can count how many times ALL numbers followed ALL other numbers in every alternate draw, every 3rd draw, every nth draw if you like. The bad news is I haven't written any simple (or complex) formula(s) that will do this and be this flexible. Formulas may be possible if you are much more explicit about your requirements, no promises though.
I believe only a suite of macros can achieve this, and some brave people have followed my instructions to create a speadsheet that does exactly this. It makes a 49 x49 table (for a 49 ball lottery) listing the counts of Ball B following Ball A for the given number of draws back in time up to date. By all means look at the screenshots, and if its what you are looking for, you could reproduce it, if your skills include using named ranges and accessing the Visual Basic editor.
Frank
Member
Actually... I remembered I had in my archives a universal ball counter. This Excel program allows you to name within a box a list of balls to track. It counts them for a specified number of draws from a named starting draw. The width of columns to count is adjustable up to seven, and it will count balls up to value 75.
It uses conditional formatting to give a visual representation grid of what is being tracked and counted. This could be used (amongst other uses) to do what you describe. It's far from a formula, it's a project. Originally written in Excel 2000 the original conditional formatting would not work in Excel 2010 so I've been re designing it in Excel 2010 so I can save it in compatibility mode for earlier versions.
Because it relies heavily on conditional formatting, the sheet is protected to prevent accidental deletion of formulas and formats. When I'm happy with it I'll upload it. It should be useful to someone.
It uses conditional formatting to give a visual representation grid of what is being tracked and counted. This could be used (amongst other uses) to do what you describe. It's far from a formula, it's a project. Originally written in Excel 2000 the original conditional formatting would not work in Excel 2010 so I've been re designing it in Excel 2010 so I can save it in compatibility mode for earlier versions.
Because it relies heavily on conditional formatting, the sheet is protected to prevent accidental deletion of formulas and formats. When I'm happy with it I'll upload it. It should be useful to someone.
Frank
Member
You add rows from the button until the highest draw number matches your results set (which you can paste in - but just results , no draw numbers. then steer the counting grid over what you want to count and name the balls you want to track.
http://www.mediafire.com/download/f4aw51o2mg10a0y/adjustable_ball_counter.xlsm
http://www.mediafire.com/download/f4aw51o2mg10a0y/adjustable_ball_counter.xlsm
Hi Frank, and sorry for not responding sooner but I'm a shift worker and I tend to work quite a bit. But what I was looking for was similar to what you can already do in lotostatisticsxls with what came before, with, and after. At present when you use this function it will give you the numbers that immediately came up after the number or group of numbers that you inputted. Not only am I interested in those numbers that came up the very next draw but also the 2nd, 3rd and maybe even 4th draw after that. I think this would then give you a group of numbers that might be highly favorable to play or to eliminate depending on the strategies(filtering) that you might be utilizing. Obviously, some of these numbers might be repeaters in more than one of the draws that came after and thus could become part of a more favorable pool of numbers to draw from. Just to reiterate, Lottostatistics only gives you values that came up the very next draw after the inputted number(s) hit. Thank you for your interest Frank and I hope you see this response.
Hi bloubul, thanks for replying. I tend to stick to a 100 draws when I'm doing any statistical analysis as it makes it easy to compute percentages in my head with out having to resort to a calculator. If I find something interesting then I'll pair it down to 50 draws and then down to 20 or so draws. Like most serious lotto players have found, when you find a trend they tend to be fleeting in nature. In my pursuit I'm trying to develop trends, artificially, so that they'll have a longer lifespan. My success have come at the lower pay scales and not consistent enough to play on a draw to draw basis. I'd like to think that if I could determine what came after a said number or group of numbers not only the next draw after, which I already can do, but the 2nd or 3rd draw after that this could definitely give the player some insight as to what might be coming down the pipe in the near future. Thank you for your interest.
Frank
Member
Thank you Larbec, if nobody talks to me I'll never know if there is a problem with it, or in your understanding of it.
It's a frequency counter. Once you have a set of past results in your results list by creating sufficient new rows to hold the full set, (most recent at the top) and pasting them in from say, a CSV listing or another spreadsheet , you can:-
Set the number of balls to be included in the process, first 5 balls per draw, first six, first seven. Basically to include or not include a bonus ball or balls. Just set the width of the counting grid.
Set how many draws to count the frequency of, between draw number A and draw number B by setting the draw number to count back FROM and how many draws to count backwards from this point.
It is very visual if the conditional formatting works, you should be able to see clearly what balls and draws are included in the frequency count. Only ball numbers that exist will be visible in the results table.
Perhaps I should have made it available with a results set already in, for demonstration purposes but as it's supposed to be universal, my set probably wouldn't be the set you prefer.
When I fill it with a full set of UK lotto results I can get either the ball frequencies with or without bonus balls over the full history of the draw, or between any two draw numbers.
Does this help ?
It's a frequency counter. Once you have a set of past results in your results list by creating sufficient new rows to hold the full set, (most recent at the top) and pasting them in from say, a CSV listing or another spreadsheet , you can:-
Set the number of balls to be included in the process, first 5 balls per draw, first six, first seven. Basically to include or not include a bonus ball or balls. Just set the width of the counting grid.
Set how many draws to count the frequency of, between draw number A and draw number B by setting the draw number to count back FROM and how many draws to count backwards from this point.
It is very visual if the conditional formatting works, you should be able to see clearly what balls and draws are included in the frequency count. Only ball numbers that exist will be visible in the results table.
Perhaps I should have made it available with a results set already in, for demonstration purposes but as it's supposed to be universal, my set probably wouldn't be the set you prefer.
When I fill it with a full set of UK lotto results I can get either the ball frequencies with or without bonus balls over the full history of the draw, or between any two draw numbers.
Does this help ?
Frank
Member
Here is the same spreadsheet with UK lotto history in it. You should see what happens when you spin spinners and how it affects the frequencies counted as displayed on the red grid.
I should add that it only counts the ball numbers you ask it to. For the UK Lotto I wanted all 49 numbers counting so thats what I filled in in the box. No point putting in numbers greater than 49 as they dont exist in this particular lottery.
http://www.mediafire.com/download/wpt4h29mhm4mos0/adjustable_ball_counter_UK_lotto_demo.xlsm
Comments please ..
I should add that it only counts the ball numbers you ask it to. For the UK Lotto I wanted all 49 numbers counting so thats what I filled in in the box. No point putting in numbers greater than 49 as they dont exist in this particular lottery.
http://www.mediafire.com/download/wpt4h29mhm4mos0/adjustable_ball_counter_UK_lotto_demo.xlsm
Comments please ..
Thanks Frank, I'm at our Cafe at the moment but this afternoon I'll download the spreadsheet. iPads suck for things besides emails and playing games IMHO. Lol. Perhaps folks don't reply because they really don't know how to use it and too embarrassed to say something. Idk though , just my thoughts. I know for me, I gave to read things several times or hear/read it a different way before it sinks in unless it's one of my own spreadsheets I have to always remember that MY SPREADSHEETS are easy to understand because I wrote them Just MHO. I'll get back with you later because I'm curious how I can app,y this with the Texas a Games or Power ball /Mega
Hi Frank, will your ball counter program list how many times the rest of the lotto numbers followed a number that just came up in the most recent draw? And can you look at more than one number at a time? Also, I'm utilizing excel97 and is the program compatible with 97? Lastly is it possible to change the parameters so that the program can look at values up to 99? Thank you for input.
Frank
Member
If you think about it, nobody can count the numbers that will follow the most recent draw because that would be in the future ?? I wish I could do that.cdrake said:
You can only count how many times number(s) followed a draw in the past, up to a more recent draw, which might well be the most recent draw. This programme certainly does that.
If there are six numbers in any draw, and you set it to count the frequencies of named balls happening AFTER your nominated draw in the past up to the most recent, then the frequencies you find for following balls apply equally to each of the six balls in your nominated earliest past draw, because the counted balls followed that draw, therefore followed the 6 balls. You cannot change that, it's a lottery fact. Yes you can choose which following balls to get the frequency of, just one ball or all of them.
To get it to work with Excel 97 might be tricky, not many people still have that, I don't have it. I do know that saving the Excel 2010 version as an Excel 97 to 2000 (.xls) file in compatibility mode is unsuccessful, as the macros use commands which were not around in 1997. The conditional formatting did survive the process and work in Excel 2000 on my Old XP machine, I'm not surprised as it was originally written in Excel 2000 before being updated.. With that in mind, using Excel 2000 I was able to rewrite the insert / delete row macros so that they do work in Excel 2000.
I have made a modification to count balls up to 99 , just needs testing to see if all works OK. If I am happy with it I'll make the Excel 2000 version available, fingers crossed when saved as 97 version it will still work, but I can't test that.
One important thing a user of any version needs to know, is that if copying and pasting a result set into this programme, it is important to use Paste Special> Values ( not Paste) since this would overwrite conditional formatting and render the counting zone invisible.
Hi Frank and thanks for your response to my query. The lotto tool that I presently have been using for the last number of years is Nick koutras "Lottostatisticsxls". I like it for its simplicity, its handy statistics at the push of a button and it's free. One of the features that I enjoy using is what came before, after, and with. I like this feature as you can look at a single number or an array of numbers and determine what number(s) came after this number(s) when they hit. So for example if the #1 hits this feature will tell you how many times each of the other 48 numbers came up the very next draw and list them by their frequency from best to least. What I was interested in being able to do was to get the same results for the 2nd draw after, the 3rd draw after, and so on. This strategy might show you a few numbers that seem to be at the top of one or more of the lists that you develop. This might result in a small pool of numbers that just might produce better than average results especially when you start employing some of your favorite filters. I'm sure if someone was able to look at the source code for this tool you could probably add a snippet of code so that this feature could be enhanced. Thanks for your interest Frank as its nice to get feedback for ideas that are presented on the forum.
Frank
Member
Well, I can't follow Nick Koutras, he was exceptional, and as far as I know his code was protected and I don't know whether the password had been cracked by anyone. I'm not inclined to even try to improve on his good work, I've cut down on lottery work.
I'm trying to get my head around what you are saying but the term 'frequency' only has meaning when the draws(s) you are counting between are clearly specified:- I must assume back to draw 1.
In order to give meaning to 'what came before, after and with' one must be, by definition diving into past history at a set draw at least one draw ago, so that there is in your list a draw that came before that draw and a draw that came after it. You cannot do that when you ask the question about the most recent draw ?
Suppose the most recent draw was 2005 then presumably you could choose draw 2004 and ask questions about the numbers in the draw after it (2005) ...Frequency since draw 1? Also ask questions about the numbers in draw 2003... Frequency since draw 1 ?
I'm also having trouble with the phrase "this feature will tell you how many times each of the other 48 numbers came up the very next draw"
If its a 6 ball lottery then only 6 balls can be involved in the very next draw, yes they are taken from the remaining 48, but there are only 6 to show frequencies for ?
The program I uploaded is not as slick, it was never intended to be. But it would do those things. Just set the draw number you are interested in (in the past) as a start draw, and set the 'count back over..' to an equal number, assuming you want freqiencies from draw 1.
You you would only need to enter the 6 numbers in the very next draw into the grey "Search for these numbers" box, and my program would display those frequencies for those numbers only. You could Copy the full result and paste special> values into the search for box. There would be no frequency sorting, but with only six balls counted it not too difficult to sort them by eye ?
Alternatively if you picked draw 2001 ( in your head only) as your draw of interest, but set the spreadsheet to start counting at 2003 and looked back 2003 draws , then copy>paste special>values the result from draw 2003 into the grey box , you will have got the frequencies of the next but one 'balls in the future' as seen from 2001. It is also the next but two from 2000, the next but three from 1999 .... your head decides that, surely ?
in the screenshot, the frequency counts shown are correct for draw 2004 (one draw in the future), draw 2003 (2 draws in the future), draw 2002 (three draws in the future) .. and so on ...
http://www.mediafire.com/view/f8ca0g6f279psfc/count.png
In this screenshot I'm interested in draw 2003 and what happenned in the draw before it 2002
http://www.mediafire.com/view/whbr767e0j2nc7b/count2.png
In this screenshot I'm interested in draw 2003 and what happenned in 2 draws before it 2001.
http://www.mediafire.com/view/zl9xhz21rb2wedx/counter3.png
Am I making any sense ? Not slick, but it does it.
I'm trying to get my head around what you are saying but the term 'frequency' only has meaning when the draws(s) you are counting between are clearly specified:- I must assume back to draw 1.
In order to give meaning to 'what came before, after and with' one must be, by definition diving into past history at a set draw at least one draw ago, so that there is in your list a draw that came before that draw and a draw that came after it. You cannot do that when you ask the question about the most recent draw ?
Suppose the most recent draw was 2005 then presumably you could choose draw 2004 and ask questions about the numbers in the draw after it (2005) ...Frequency since draw 1? Also ask questions about the numbers in draw 2003... Frequency since draw 1 ?
I'm also having trouble with the phrase "this feature will tell you how many times each of the other 48 numbers came up the very next draw"
If its a 6 ball lottery then only 6 balls can be involved in the very next draw, yes they are taken from the remaining 48, but there are only 6 to show frequencies for ?
The program I uploaded is not as slick, it was never intended to be. But it would do those things. Just set the draw number you are interested in (in the past) as a start draw, and set the 'count back over..' to an equal number, assuming you want freqiencies from draw 1.
You you would only need to enter the 6 numbers in the very next draw into the grey "Search for these numbers" box, and my program would display those frequencies for those numbers only. You could Copy the full result and paste special> values into the search for box. There would be no frequency sorting, but with only six balls counted it not too difficult to sort them by eye ?
Alternatively if you picked draw 2001 ( in your head only) as your draw of interest, but set the spreadsheet to start counting at 2003 and looked back 2003 draws , then copy>paste special>values the result from draw 2003 into the grey box , you will have got the frequencies of the next but one 'balls in the future' as seen from 2001. It is also the next but two from 2000, the next but three from 1999 .... your head decides that, surely ?
in the screenshot, the frequency counts shown are correct for draw 2004 (one draw in the future), draw 2003 (2 draws in the future), draw 2002 (three draws in the future) .. and so on ...
http://www.mediafire.com/view/f8ca0g6f279psfc/count.png
In this screenshot I'm interested in draw 2003 and what happenned in the draw before it 2002
http://www.mediafire.com/view/whbr767e0j2nc7b/count2.png
In this screenshot I'm interested in draw 2003 and what happenned in 2 draws before it 2001.
http://www.mediafire.com/view/zl9xhz21rb2wedx/counter3.png
Am I making any sense ? Not slick, but it does it.
Frank
Member
Alternatively, in a different sheet just keep a running total of the frequencies of the drawn balls, draw by draw... like this ..
http://www.mediafire.com/view/9q516wzc54g6385/FReq.png
Look them up anywhere before, during or after any past draw. That is a relatively simple formula to create...
http://www.mediafire.com/view/9q516wzc54g6385/FReq.png
Look them up anywhere before, during or after any past draw. That is a relatively simple formula to create...