Heat Chart for Lotto / PowerBall

[Frank]

Now I'm going to stir the "SNAKE BAG", Is "EXCEL" Superior to any Lotto Software or not?????

BlouBul

Yes. The sky is the limit to what you can do with it. However it's not as user friendly as lotto software. Like all instruments, you have to learn to play it first, and many people find that too daunting. The only way is to use it every day except for holidays. It's a lot easier to learn now than it's ever been. Now you just google what you want to do and somebody will have an answer! 25 years ago it was awful, Excels help files were not that much help, you needed a training course to get started.

 

[khanaran]

Hi Frank

Just a little questions

I am using your template for the heat maps and together with advice and help fro EJ (Bloubul) am getting the feel for using the template for UK49 and PB south africa.
My question will it be possible time permitting for you to convert the template for the pick3 that we have here now

REgards

Raj

 

[AllenB]

It's all in the Spreadsheet. I started with Visicalc on an old Hp Desktop with a 4" Screen around 1980. I moved from there to Lotus123 (messed a bit with Quatro) and finally to Excel in 1984. Excel lets me do about any setup that I can think of and notwithstanding the occasional typo in a formula, get what I am looking for. Other Data Base programs do the same or more; but I do not know them like Excel.

 

[Frank]

Hi Frank

Just a little questions

I am using your template for the heat maps and together with advice and help fro EJ (Bloubul) am getting the feel for using the template for UK49 and PB south africa.
My question will it be possible time permitting for you to convert the template for the pick3 that we have here now

REgards

Raj

It might be a little question, but its not a little job! You are pushing your luck Raj!

However, I have made a few assumptions, since I dont do Pick 3, we dont have that type of pick 3 in the UK.
There are only 10 digits, but order is important, so both value and position can be classified as a parameter to measure heat for. This creates 30 possible outcomes of digit and position combination. So 1a would be digit 1 in the first position, 4c would be digit 4 in the third position etc.

I have modified that original template somewhat, because it was a little inflexible, not much room in the first columns for expansion without rewriting macros. So all the tracking of skips and also a 0 or 1 grid are on sheet 2. There is also a list a list of test results which i generated. This version has the latest or newest result at the bottom of the list. Formulas look for the last 24 results, and it is this info which is displayed on sheet 2.

Now you have a choice, to use skips or to use a hit/miss grid. On sheet 1 are two new buttons, which you use to choose which source of data to use., skips or grid. Clicking the button of your choice copies and paste-links the appropriate heat scores into column N on sheet 1. The lookup table is upside down, a consequence of it needing to be in order descending in the first column. This is a side effect of using a 0' or 1' grid which means high score = hot, the opposite of what we had before. This played havoc with placement in the final heat table, I had to create an upside down sub total, used to assign rows to the numbers in the table. The heat scores derived from skips have been 'inverted' to match the philosphy of high score = hot as well.

Anyway Ive had enough of heat charts now, my job is done, I wont be amending or creating any more. I'm off to the pub.

https://www.mediafire.com/?gjst188r11qs6nc

 

[khanaran]

Hi Frank

Thank you so kindly sir

Yes I know I have been pushing my luck with and appreciate all that you have done for me.

I would sincerely like to send you something from here as a token of my appreciation for all that you do for all here at Lottoforums.

If I am allowed to post my email address I will post a link or alternatively can you pm me your details please

Thank you once again

Raj

 

[Frank]

Thanks, but its not necessary. We are not allowed to post contact details here and PMs dont exist here either. We are in enforced excommunicado in this forum. You just keep plugging away at Excel so you dont need my help. Thats reward enough.

 

[Sharks001]

Frank

Hi awesome work on your sheet.....may I ask you what was your success rate with your UK Lotto by using your Heat chart? Problem I see you will never know what temperature hit you cannot take on white hot will hit every draw so its still stay picky to choose what to play and what not. But awesome work you have done here. Would be interesting to know and hear your success rate by using it. Also 4 skips is a little bit to less history....like u said 12 will be more accurate. Looking forward to hear your reply. I like it though....some good logic in it

 

[Frank]

Hi Sharks,

I don't follow your comment about white hot hitting every draw, that's not what happenned once I'd settled on a representative set of FIXED upper and lower limits for the lookup table. Don't be tempted to just plug the max value calculation into the table white hot value to force it to update every draw, that would mess things up.
I was using the table of 0's and 1's to calculate my heat scores for the 649. So high score was hot, low score frozen.
If you decide that a definition of white hot is two consecutive recent hits plus say a hit five draws ago AND it's frequency is more than the norm, say generating an Occurrence figure of 54 ( Old uk 649) you can work out what score white hot would be. On a look back grid of 12, the recency rating would be 12+11+0+0+8 = 31. It's total score would be 54+31 =85. I would set that as white hot and forget about it for years! If white hot scores came in it would show, if not, it wouldn't. Same logic for frozen, a well thought out FIXED value of low temperature score worked for me.

I never actually used that method to predict the UK lotto, I had my finger in too many other pies and ideas of lottery research. It was simply a website 'at a glance guide' to what was going on, for visitors to my site.

What I have not disclosed on here yet is that I used to copy each heat chart, 'paste values' and 'paste formats' so that when I added a new result, and the chart updated I could see where the new result balls were previous to the draw, ( conditional formatting came in handy here). So I could imagine them jumping up the chart from specific temperature zones to where they are on the updated chart. You might infer that balls in similar zones would also be chosen in the next draw.

Thats how I would use it, and how I have it set up for the new UK 659 lotto.

I hope this helps,

Frank

 

[Sharks001]

Hi Sharks,

I don't follow your comment about white hot hitting every draw, that's not what happenned once I'd settled on a representative set of FIXED upper and lower limits for the lookup table. Don't be tempted to just plug the max value calculation into the table white hot value to force it to update every draw, that would mess things up.
I was using the table of 0's and 1's to calculate my heat scores for the 649. So high score was hot, low score frozen.
If you decide that a definition of white hot is two consecutive recent hits plus say a hit five draws ago AND it's frequency is more than the norm, say generating an Occurrence figure of 54 ( Old uk 649) you can work out what score white hot would be. On a look back grid of 12, the recency rating would be 12+11+0+0+8 = 31. It's total score would be 54+31 =85. I would set that as white hot and forget about it for years! If white hot scores came in it would show, if not, it wouldn't. Same logic for frozen, a well thought out FIXED value of low temperature score worked for me.

I never actually used that method to predict the UK lotto, I had my finger in too many other pies and ideas of lottery research. It was simply a website 'at a glance guide' to what was going on, for visitors to my site.

What I have not disclosed on here yet is that I used to copy each heat chart, 'paste values' and 'paste formats' so that when I added a new result, and the chart updated I could see where the new result balls were previous to the draw, ( conditional formatting came in handy here). So I could imagine them jumping up the chart from specific temperature zones to where they are on the updated chart. You might infer that balls in similar zones would also be chosen in the next draw.

Thats how I would use it, and how I have it set up for the new UK 659 lotto.

I hope this helps,

Frank

Hi Frank

Frank thank you for the in depth explanation ......Just 99% of people will presume white hot is due for the next draw....which is not the case. At the end of the day it all depends of the value of the skips of the temperatures....different story if white hot is a skip that's due then it will have a good probability to come in for the next draw....so it all depends on the skip value at the end of the day....example skip 7 is due...and skip 7 number value is 13 but its in cold.....then cold have the best possibility to appear.Thank you for your in depth explanation Frank

 

[AllenB]

Frank,
I should have looked at Your Tempsorter 3 file when you posted it. Unfortunately I was doing my own thing. I just downloaded your work and once again it is very impressive and well organized. As I have been a bit perplexed with my own Heat Calculations I am exited to incorporate your idea.

For Pick 3, I look at the 10 scores by position. My process separates the scores into 3 zones for each position. The Bottom 3 are Cold, Middle 4 Warm and Top 3 Hot. I will use the Scores From The 0/1 Grid on Sheet 2 with an indirect-match formula to create a new table that sorts the numbers by Rank and then assigns the Zone. I will compare the results with the other methods I have used to see what happens and hopefully settle on a temperature score that will be "easier" to predict.

Thanks again for You Amazing Work

 

[AllenB]

Frank. I just wanted to ask You about how I am using your Heat Scores. I have assumed that the adjustment of the Max/Min Values are not necessary because, dividing into Zones by Rank there is always an 3-4-3 distribution of Digits. I am doing the same thing with the Skip Sequence Scores. Combining both "Zone Codes" to form a pair, I can run the Pair History and produce a set of Straight triads from a sequence of 3 pairs (1 from each position).
I created a Macro that lists The Zone for each digit by position for the last 300 Draws. Looking at a graph for each Digit-Position shows its Migration from Zones 1-3. In the Graph I overlay the actual skip for the same digit-Position because it is one way of showing the Hits. The First thing I noticed from the Digits in Position 1 is that a digit seams to stay in a Zone for a long time. A hit does not always cause Zone Change. I think this could be an important fact that can be a "Trigger". More Study, More Graphs, and surely more questions to follow.

In the 0/1 Grid, You are showing 1000 Draws in descending order. From the "Offset" formula it appears that only the 25 most recent draws (at the Bottom) are used in this Table. The Recency Value for some Digits can be 0. It looks like the Recency, Frequency, Occurrence and heat Scores come from the 25 Draws and the other 975 are not involved in the Score. Am I missing something here? It would appear that the 1000 Draws are used to generate the Skip Sequence in the "Skip Heat Score". Here again it looks like You are only using the Last 24 Draws. I apologize for being a bit Lazy, and not looking deeper; but, could you explain how the "Extra" Games affect the Outcome?

 

[AllenB]

I noticed something else that puzzles Me. The Skip Table by Columns does not agree with my data. I keep a running count of each Digit-Positions Skip. It looks like the Table should agree with that list but it does not. For Example Digit 1 Position 1 has a Current Skip (If it Hits) of 21 in my data and 4 in Yours. I know my skips are correct so I am wondering if the values in your table are based on something else that I am to Lazy to look for.

 

[AllenB]

Disregard last Post. I did not Reset the Game correctly. My Table agrees with yours.

 

[AllenB]

Looking at your Skip Table you are using the last 5 skips to calculate values. Numbers currently out over 5 games will be Ranked only on their current skip. Numbers that hit in the last 5 games Vary depending on their previous skip value. What do you think about changing the values in the skips transferred columns to the Previous 5 skips like your original Sorters?

 

[Frank]

Allen, just a quick reply from my phone to tell you I've read your posts. It's a glorious spring weekend here in the U.K. So I'm catching up with the garden, haven't had time to fire up my laptop to look at my spreadsheet to put your question in context. I'll get back to you when I can.
all the best, Frank

 

[AllenB]

Thank You, I understand. I have an appointment with my John Deere La120 today. I've got about 3 Acres left to mow.
Looking at the Frequency Calculations I am confused. It part because, I have never used the Frequency function and because it does not return the Frequency I am used to.
Enjoy, No Rush. This is a Marathon of unknown duration, Not a Sprint.

 

[Frank]

Hi Allan, It is clear that youve put more thought and effort into applying this to the pick 3 than I have, as I said previously Pick 3 is not of real interest to me as its unavalable in the UK. My experience over the years has been with 649 using the 0,1 grid and the statistics being different at 14 million to 1 is likely to produce a much more varied temperature spread than a lottery with odds of 999 to 1 drawing 3 numbers would. Thats why my temperature chart has 14 zones, otherwise it would have to be very wide to hold all the balls in the middle range.
My experience in manipulating skip values to generate heat scores is very little, its as new to me as it is to you. Thats why the spreadsheet I created for Khanaran has a lot of 'spare' columns on sheet 2 to allow for (if necessary) more skips than 5 to be taken into account. it can be extended to look back at 14 skips without disrupting the macro which copies/pastes the heat score onto sheet1. I suggest there might be little point in looking back further than that given the way that the weighting grid currently works.
I really don't know as the idea of using raw skips only cropped up as a hasty way of doing a demo sheet, it was not supposed to suggest it was the right way and I dont particularly like it.

You noticed that the skip calculation grid on sheet 2 looked at the most recent 25 draws, but most of them were not in use since currently only 5 draws feature in calculations. To answer your question what effect do the 'extra draws' have the answer is none at all. They are only there in case needed to extend the lookback period at a later date. I note that you are not using 14 temperature zones as I designed it, which sounds like a good idea given the reduced spread of temperatures. I can't comment on that as Im not seeing the results.
Ive had another look at that spreadsheet, see the next post.

 

[Frank]

Since I wrote the above I spent a little time testing out a modified skip table which ignores the counting process in the table and only uses the attained skip value at the end of an absence run. So its a bit like the 0 and 1 grid method, except the '1' would be replaced by the attained skip value before it was drawn. The problem with this, is that I require more recent frequently drawn balls to be hot (high score), so high skip values in the table would represent the return of a very cold digit, (as opposed to a hot digit) which shouldn't necessarily have a high score - and this fact needs to be acknowleged. Attained skips in the past are also worth less than more recent ones too. So I decided to 'invert' all attained skip values by subtracting them from a fixed high skip value of 75.

So to work out a score for a digit just drawn now (0 in column BU):-

What skip has it just recovered from ? Example, it was 5 before it was drawn. Its score is therefore 75-5= 70 entered into the first column of the weighting grid.

But a digit drawn say 3 draws ago recovering from a skip of 5 would have a score of 75-5-6 =64 . Why 64 ? because I decided that older results need their score further reduced by 2 times how far back it was. 2 times 3 draws back, reduce by 6. The 2 times is the diff value (-2) entered into cell CO1 on my modified sheet.
My amended table now looks back 18 draws but bears in mind what the skip was 19 draws ago in order to get a score for 18 draws ago.
Having done this I totalled the scores, but because the sheet also could use the 0,1 grid method (heat range about 12 to 120) I decided to divide the final scores by 3 to bring them into line with the other system.

I still wasn't happy with the distribution of results, there was still a large disparity between white hot and the rest of the field, which seemed to cluster in the bottom cold half of the table. I believe this to be a consequence of the few digits drawn on each occasion. I had exactly this problem with the 5/50 Euromillions results, using a 0,1 grid to generate scores. I decided the problem was the temperature zones in my 14 zone table were all equal in range, and perhaps should be wider in the center temperature zones than at the extremes. I solved this by dividing the overall temerature range (max - white hot value minus min frozen value) by 39 which creates (3 times the 13 steps above frozen) of available shares of (temperature range) for the zones. I shared out these differences to favour the central part of the table and this cured that problem.

https://www.mediafire.com/?t33m6gbsofub6vz

Thats all I know about pick 3, its not my sphere, I'm done with it now, I hope you can make yours work as you wish.

Regards,
Frank

 

[AllenB]

Excellent Work Frank
You have Taught Me Much.
Thank You

 

[khanaran]

Thank you Frank

I am also learning from you.

You have been a great help to me with all the questions that I have asked and had them answered

Thank you once again

raj