Heat Chart for Lotto / PowerBall
- Thread starter bloubul
- Start date
Frank
Member
Bloubul,
I explained to you via email after your enquiry last year that I dont yet have access to the original spreadsheet that generated the temperature zone graphic displayed on my website, due to hard drive failure on that PC and the backup being corrupted. Even if I had I can tell you that it required knowlege of lookup tables and Macro programming in order to extract and translate a temperature score to that graphic layout. Thats a fair amount of work if you dont have those particular skills, and I wonder if it is worth it. I do not intend to reproduce that graphic now that my website is abandoned ( and looking worse for wear).
http://www.lotterygen.co.uk/temperature_page.html
I would suggest that if you are not running a website you do not need to produce a layout in that format anyway, all you need is a table of heat scores and a way of grouping your numbers within the defined limits of temperature zones you have defined. That is if its for your own personal use.
The easiest way is as Icewynd suggests, to use conditional formatting, except that if you have ten temperature zones like I did, you need ten conditional formatting rules applied to each ball in order to highlight each ball according to your temperature limits. That can be tedious if you are not used to conditional formatting.
However, once conditionally formatted you can copy and paste the table and sort by 'temperature score' - this will produce colour 'bands' where the balls have been grouped adjacent to each other according to score and hence colour.
Apart fom that you need a system which allows you to create a temperature score in the first place, there are loads of ways of doing this, limited only by your imagination. Usually it will be based on skips or frequency or a combination of both. For my website I used a weighting system that took into account not just the fact that a ball had been recently drawn, but how recently, and a figure was calculated accordingly. Have you done this ?
I'll be posting a demo spreadsheet shortly with a just one way of doing it. It incorporates some of the ideas I had 23 years ago when I first did this, but not exactly the same, and I wont be writing any macros.
Frank
I explained to you via email after your enquiry last year that I dont yet have access to the original spreadsheet that generated the temperature zone graphic displayed on my website, due to hard drive failure on that PC and the backup being corrupted. Even if I had I can tell you that it required knowlege of lookup tables and Macro programming in order to extract and translate a temperature score to that graphic layout. Thats a fair amount of work if you dont have those particular skills, and I wonder if it is worth it. I do not intend to reproduce that graphic now that my website is abandoned ( and looking worse for wear).
http://www.lotterygen.co.uk/temperature_page.html
I would suggest that if you are not running a website you do not need to produce a layout in that format anyway, all you need is a table of heat scores and a way of grouping your numbers within the defined limits of temperature zones you have defined. That is if its for your own personal use.
The easiest way is as Icewynd suggests, to use conditional formatting, except that if you have ten temperature zones like I did, you need ten conditional formatting rules applied to each ball in order to highlight each ball according to your temperature limits. That can be tedious if you are not used to conditional formatting.
However, once conditionally formatted you can copy and paste the table and sort by 'temperature score' - this will produce colour 'bands' where the balls have been grouped adjacent to each other according to score and hence colour.
Apart fom that you need a system which allows you to create a temperature score in the first place, there are loads of ways of doing this, limited only by your imagination. Usually it will be based on skips or frequency or a combination of both. For my website I used a weighting system that took into account not just the fact that a ball had been recently drawn, but how recently, and a figure was calculated accordingly. Have you done this ?
I'll be posting a demo spreadsheet shortly with a just one way of doing it. It incorporates some of the ideas I had 23 years ago when I first did this, but not exactly the same, and I wont be writing any macros.
Frank
Frank
Member
Well, the only issue is how long it takes to fully answer the question. One could just say
1. work out a temperature score
2. assign words to those scores
3. sort them
4. find a way of displaying the results.
And there you go !
However, I'm not the kind of person to opt out of a challenge, even if it takes up a fair amount of my time, when I have any.
The above is the essence of what needs to be done, and I've done a couple of spreadsheets that do it for 649 (fake data), 659 and Powerball (fake data). However the input data and the most important part of the sheet is column K on each sheet.
In order to create some input data my sheets do some calculations in columns C to J, but your system may use different criteria and arrive at figures in column K differently. You could paste in some values.
My sheet uses a lookup table to convert the heat scores to text descriptions, e.g "cooking". Once this is done a recorded macro copies the relevant data from the first table, pastes it into a new table starting in column N and sorts it all according to heat score ascending. As previously discussed this new table has ball numbers conditionally formatted according to the text word that accompanies it. This produces color bands down the table.
However, the lookup table is dynamic ( meaning it needs boundary information entering to function)
The heat scores in column K have their max and min values calculated (cells below that column of numbers). Figures at or around these values need entering into cells T21 and T22 to set up the upper and lower bounds of the lookup table.Intermediate values are then calculated. The true boundary figures I found by trial and error with the help of the column graph, the object is to get a bell curve distribution of balls. I fixed the lower bound (white hot) as zero and used the 'Min' value of the table to set 'red hot' in the lookup table.
After that ,its just a matter of how to display the results:- and in 1997 I resorted to using a macro to tabulate the temperature zones. This time Ive used formulas to inspect information in and adjacent to the look up table to work out what row and where to put each ball in an output table.
There are some notes on each spreadsheet too. Note, as I said it does contain one macro which sorts the data.
I'll leave it at that and see if there are any questions.
http://www.mediafire.com/file/i3ra5esnjz68jiu/Tempsorter_659_PB.xlsm
http://www.mediafire.com/file/6hubid1aii2vbbx/TempSorter_649.xlsm
1. work out a temperature score
2. assign words to those scores
3. sort them
4. find a way of displaying the results.
And there you go !
However, I'm not the kind of person to opt out of a challenge, even if it takes up a fair amount of my time, when I have any.
The above is the essence of what needs to be done, and I've done a couple of spreadsheets that do it for 649 (fake data), 659 and Powerball (fake data). However the input data and the most important part of the sheet is column K on each sheet.
In order to create some input data my sheets do some calculations in columns C to J, but your system may use different criteria and arrive at figures in column K differently. You could paste in some values.
My sheet uses a lookup table to convert the heat scores to text descriptions, e.g "cooking". Once this is done a recorded macro copies the relevant data from the first table, pastes it into a new table starting in column N and sorts it all according to heat score ascending. As previously discussed this new table has ball numbers conditionally formatted according to the text word that accompanies it. This produces color bands down the table.
However, the lookup table is dynamic ( meaning it needs boundary information entering to function)
The heat scores in column K have their max and min values calculated (cells below that column of numbers). Figures at or around these values need entering into cells T21 and T22 to set up the upper and lower bounds of the lookup table.Intermediate values are then calculated. The true boundary figures I found by trial and error with the help of the column graph, the object is to get a bell curve distribution of balls. I fixed the lower bound (white hot) as zero and used the 'Min' value of the table to set 'red hot' in the lookup table.
After that ,its just a matter of how to display the results:- and in 1997 I resorted to using a macro to tabulate the temperature zones. This time Ive used formulas to inspect information in and adjacent to the look up table to work out what row and where to put each ball in an output table.
There are some notes on each spreadsheet too. Note, as I said it does contain one macro which sorts the data.
I'll leave it at that and see if there are any questions.
http://www.mediafire.com/file/i3ra5esnjz68jiu/Tempsorter_659_PB.xlsm
http://www.mediafire.com/file/6hubid1aii2vbbx/TempSorter_649.xlsm
Thank You For this Frank. I am looking at the 649 file
I am going to try it on CA Fantasy 5 (5/39).
The "Fake" Numbers in the Tables seam obvious enough. I can get that from my skip calculations. I was wondering about the calculate Values for the weighted score. Looking at the 2 right columns, the formulas include the data from the first 2 columns. Is this on purpose?
I am going to try it on CA Fantasy 5 (5/39).
The "Fake" Numbers in the Tables seam obvious enough. I can get that from my skip calculations. I was wondering about the calculate Values for the weighted score. Looking at the 2 right columns, the formulas include the data from the first 2 columns. Is this on purpose?
Frank
Member
Hi Allen, I see what you mean. No, I should really have had some dollar signs in the MAX(C4:F4) part of the formulas in cols H,I,J making sure the weighting was only added to the MAX of the first 4 columns. It was only a quick example of weighting, you could use any method you see fit, its trial and error to try and balance the graph.
Whilst we are doing corrections, there is also an error in the formula for cell Y6 which should read
=IF($U6<Y$5,"",OFFSET(NPball,Y$5,0)). All the other cells on that row up to AP6 need that formula copying along the row. The error didn't show up util there were some white hot balls, which is why I missed it. That applies to both the formats 659 and powerball on that spreadsheet. The other spreadsheet for 649 is correct in that respect.
I'll re upload corrected versions when I get the chance.
Whilst we are doing corrections, there is also an error in the formula for cell Y6 which should read
=IF($U6<Y$5,"",OFFSET(NPball,Y$5,0)). All the other cells on that row up to AP6 need that formula copying along the row. The error didn't show up util there were some white hot balls, which is why I missed it. That applies to both the formats 659 and powerball on that spreadsheet. The other spreadsheet for 649 is correct in that respect.
I'll re upload corrected versions when I get the chance.
Frank
Member
Corrected versions uploaded. Having had a play, the original version of the weighting formulas emphasised the weighting and pushed the bell curve more to he centre for UK 659. If anything I'd add more to cols I and J to force cool the balls more and move the curve to the right. Theres no right answer, its a matter of playing about with it ! 
Frank,
This works very nicely on Ca Fantasy 5. The Setup did not take to long. It looked a bit weird using the Max and Min so I found that subtracting 40 from the Max and adding 1 to the Min produced a Uniform Curve.
I am in the process of setting up a back test to see where each number comes from on the Tep Chart for the game being tested.
Thanks for the work.
This should dove tail nicely with some of my other workouts.
AllenB
This works very nicely on Ca Fantasy 5. The Setup did not take to long. It looked a bit weird using the Max and Min so I found that subtracting 40 from the Max and adding 1 to the Min produced a Uniform Curve.
I am in the process of setting up a back test to see where each number comes from on the Tep Chart for the game being tested.
Thanks for the work.
This should dove tail nicely with some of my other workouts.
AllenB
Frank,
I really like this set-up. I have Taken the 649 File and converted it to analyze 5/39 and Pick 3 numbers By position.
One thing I noticed about the set-up is that the first column "This Draw" calculates each numbers score as if it Hits. It seams like it might be beneficial to use the last 4 actual skips and then list the current Skip next to the Number. That way you can see a White Hot that looks like it has flamed out instead of which number will be white hot in the current game. Maybe it is beneficial to look at it both ways, I am just thinking out load, it is late.
I just wanted to thank you for this Cool Tool
Allenb
I really like this set-up. I have Taken the 649 File and converted it to analyze 5/39 and Pick 3 numbers By position.
One thing I noticed about the set-up is that the first column "This Draw" calculates each numbers score as if it Hits. It seams like it might be beneficial to use the last 4 actual skips and then list the current Skip next to the Number. That way you can see a White Hot that looks like it has flamed out instead of which number will be white hot in the current game. Maybe it is beneficial to look at it both ways, I am just thinking out load, it is late.
I just wanted to thank you for this Cool Tool
Allenb
Frank
Member
AllenB, Well I'm pleased it works for you and you are enjoying it. People here don't often acknowlege downloading other people's work, and to get some feedback and thanks is a rare bonus. Good luck with it and all your ideas.
I hope the other 30 and rising downloaders are getting some use out if it too.
I hope the other 30 and rising downloaders are getting some use out if it too.
By the Way, I noticed that the Temp Table in the 649 File has an error in the distribution calculation. The Same in the PB file is Ok. The tip_Off was that the Icy Cold Value is greater than the Frozen Value in the 649 Temp Table. You may have already fixed this in the New File. I am using the Original with these changes.
Lots of Work to be done.
Feeling Good
Thanks again.
Lots of Work to be done.
Feeling Good
Thanks again.
