Excell Skip Hit Chart

cdrake

Member
Good morning everyone. I would like to generate an excel chart that lists how many times a lotto number(1-49) hit at each and every skip for how many draws utilized. If possible I would like to highlight the cell to show where the number sits currently. Lottostatistics.xls does this but its a locked program and it doesn't work with some of my data sets due to a error6 overflow. A simple excel formula might be all that I need to accomplish this...I hope. Thank you for your time and interests.
 

Icewynd

Member
Hi cdrake,

This thread shows how I do my skip charts in excel.

http://www.lottoforums.com/lottery/lotto-tips-strategies/13207-i-need-help-draw-skips.html

Hope that is helpful.

Good luck!
:thumb:
 

cdrake

Member
Thanks Icewynd. Sorry I didn't reply sooner but my work schedule takes me away from the computer from time to time. I'll check it out.
 
I created these 2 Excel files (2007, 2010, and 2013 versions) for Skips and Hits for the PowerBall Game and MegaMillions Game

Here are the download links:

https://app.box.com/s/x3pqvdl0riv3e2wy7vfk

https://app.box.com/s/c033vybz7toq25wr5y7c

Instructions:

Save both Excel files to a new folder. You can name the folder anything you want.

Open the Excel file PowerBall.xlsm. You can add a new game by just clicking the Add New Draw Toolbar. Add new drawing in cell E20 through J20. All draws will appear on line 20. Date is automatically updated.

Open the 2nd Excel file NumbersSkipHitss.xlsm. There are 2 worksheet tabs at the bottom. Once for the white balls 1 through 59 and a second worksheet for just the PowerBall number 1 through 35.

The Hits and Skips are calculated using all the draw history so you can adjust the number of past draws by entering the draw in cell

The Matrix changed after January 15, 2012 to a 5/59 1/35 game so you can enter 296 in cell B2 or either the worksheets.

I created these 2 Excel files (2007, 2010, and 2013 versions) for Skips and Hits for the MegaMillions Game 1/75 1/15

Here are the download links:

https://app.box.com/s/8uxqb9kd0a8ug5ptxoby

https://app.box.com/s/o2hmy5hy3lo3pu3r5y5w

Instructions:

Save both Excel files to a new folder. You can name the folder anything you want.

Open the Excel file MegaMillions.xlsm. You can add a new game by just clicking the Add New Draw Toolbar. Add new drawing in cell E20 through J20. All draws will appear on line 20. Date is automatically updated.

Open the 2nd Excel file NumbersSkipHits.xlsm. There are 2 worksheet tabs at the bottom. Once for the white balls 1 through 59 and a second worksheet for just the MegaMillions Bonus Ball Number 1 through 15.

The Hits and Skips are calculated using all the draw history so you can adjust the number of past draws by entering the draw in cell

The Matrix changed after October 19, 2013 to a 5/75 1/15 game so you can enter 112 in cell B2 or either the hit & skip worksheets. The 112 is the number of draws that use the current matrix 5/75 and 1/15.
 

dawson

Member
dawson

Back to play again.
Knew Dennis Bassboss, and Gilles D.
Have some info on Lotto max that is good. Won a house a while back that I still live in.
 

dawson

Member
dawson

My betting partner is from Sri Lanka--has 2 Masters Degrees. Mathematics and Computer Science. We are tight as the bark on a tree. How do I start to post again? I forgot.
We specialise in Lotto max and they call me Mr. Lottario.
 

cdrake

Member
welcome back to the board

Welcome back to the forum. It's always nice to get new people coming back to the forum with fresh ideas. Hope to converse with you in the near future. Bye for now.
 

larbec

Member
I am curious, do these charts really help you determine what to play next? If so can you explain your thought process

Thanks!!
 

Icewynd

Member
Hi Larbec!

I think hit-and-skip charts help -- as much as anything! There is a tendency for the most recently hit numbers to hit again in any lotto game. I look at 4 groups of skips: 0-4 skips, 5-10, 11-15 and 16+. 0-4 is where at least half of the hits come from and often all the hits fall below 10 skips. The "cold" groups don't hit much at all, depending on how many numbers they have in each group. Of course, the number of hits by skip category will vary depending on your game matrix -- these are for 6/49.

Good Luck!
:thumb:
 

cdrake

Member
I'd like a simplified excel formula for calculating just the current skip for each number. I don't want a sheet full of every skip that a particular number has hit at as it takes up way too much room on my table. Just the number and its current skip value. Thanks for the help everyone.
 

Icewynd

Member
cdrake said:
I'd like a simplified excel formula for calculating just the current skip for each number. I don't want a sheet full of every skip that a particular number has hit at as it takes up way too much room on my table. Just the number and its current skip value. Thanks for the help everyone.
Well, you need the last skip to calculate the current skip, but I guess you could just count back and see how long it has been since each number hit. You will need to assign a draw number for this to work -- if you don't know the draw numbers just assign 1 to your first draw and add one to each new draw.

=ABS((LOOKUP(2,1/($A$1:A100=A101),$B$1:B100))-B100)

You will need to modify the formula for each digit in your lottery -- this formula assumes draw number is in column A and digit 1 in column B.

Good Luck!
:thumb:
 

Frank

Member
Icewynd, I'm going to have to ask you about this formula, as it intrigues me.
=ABS((LOOKUP(2,1/($A$1:A100=A101),$B$1:B100))-B100)

I'm more used to using VLOOKUP or HLOOKUP and never had an occasion to use LOOKUP. What I find puzzling is the second field in the formula, the 1/($A$1:A100=A101) part. The format of the LOOKUP command is normally
=LOOKUP(number you are searching for, in this column, and return corresponding value found in this other column).

However you are using 1/($A$1:A100=A101) which is a mystery to me. Its a strange expression. What is it doing? How does this formula work ? I can't get it to work, it produces N/A .
Also if there are six drawn numbers in the lottery, almost any of the columns might contain the latest ocurrence of a particular number, so how is this catered for ? I actually gave up and wrote a macro instead! :rolling:
 

Icewynd

Member
=ABS((LOOKUP(2,1/($O$8:O2479=O2480),$G$8:G2479))-G2480)

This formula is from my Pick 3 data base. What is does is look back over a column of 3 digit numbers that have been drawn in the past to find a match with the current drawn number. Then it calculates the absolute difference between the draw number for the matching past draw number and the current draw number to give a "distance" between the two draws.

eg.
Draw # Result Draws since hit
1 001 N/A
2 326 N/A
3 554 N/A
4 326 2
5 001 4

I am afraid I can't explain the role of the slash in the formula as I obtained it through a web search from, I believe, one of the Excel advice forums.
 

Icewynd

Member
Well, my column headings messed up. The three columns are
1. Draw #
2. Result
3. Draws since hit

With reference to CDrake's request, I don't see why this formula wouldn't work to scan up a column of drawn numbers to find out the "distance" or skip since the last hit.
 

Frank

Member
Thanks for the clarification, Icewynd. I think I found the original source you got the formula from and it is much clearer to me now what is going on and how it works. The 1/($O$8:O2479 part means look upwards not downwards, and the =O2480 means this is the number you are looking for when you look up that column.
I have come to the conclusion that you need a formula for each number and each column , so if there are 6 drawn balls and 49 numbers you need 6 x 49 formulas initially, then you need another 49 formulas to work out which of the 6 columns has the most recent draw number and then calculate the skip. I have already done this for two scenarios, one where the most recent draw is at the top (uses VLOOKUP) and one where the most recent draw is at the bottom (uses Lookup withe column inverted as above).
I will report back when I have more time to explain it. :)
 

cdrake

Member
Thanks for the replies

I'd like to thank all those who responded to my inquiry. Been away from the computer fora little bit as work always seems to get in the way. I'll look at Icewynds formula and see how it'll behave with what data that I'm trying to analyze. Thanks again everyone.
 

cdrake

Member
Tried the formula

Tried the formula but haven't gotten the results that i wanted. I put the draw numbers in column A from 1 to 20 and in column B I then put 20 data points from one column of data from a 649 file just as a test. I then put in the formula as described and changed the letters to reflect where the data was located. All i got when I copied the formula down in column C was #NA followed by 19 zeroes. I looked at ICEWYND'S example and I can follow how this formula should work but I must have missed something. I'll keep looking as I must have goofed someplace. Is the "2" In the formula =abs((lookup(2,1/ etc. the number that is being looked up? Thanks for the help everyone.
 

Frank

Member
No, the number 2 has nothing to do with your data. It's an Excel switch that just signifies that you are using the function in a special way. Provided you have your latest result at the bottom of your data, then the address after the equals sign in that formula contains the number you are searching for, that is the number in your latest result for the specific field column. So that expression would = B20, assuming that cell is the last of your data.

So. If you happenned to have a number 6 in that cell, then the expression is looking for number 6.

You haven't told us yet what format of lottery you are tracking. Is it 6/49?
 

Frank

Member
I worded the above incorrectly, sorry for any confusion. Lets say you wanted to find the skip of number 9. Then you would have number 9 in a cell elsewhere, lets say you have a row of cells A2479 to Z2479 containing the numbers 1 to 49. So if you were wanting to know the skip of number 9, that would reside in cell I2479.

so the cell address you enter after the equals sign in that formula is I2479 to find the skip of number 9 in that particular column.

I hope this is clearer. :)

I have my row of numbers 1 to 49 at the top of the spreadsheet. I'll upload an example soon. Unfortunately because this formula only looks up one column, there needs to be other formulas looking up the other columns, so this complicates the final version of the formula. It is further complicated for conditions when the number being searched for is not found, as you cannot perform arithmetic when N/A is returned.
 
Top