Calculating Average Skip
- Thread starter bloubul
- Start date
As Far as I know, There is not a single formula that will do this. That said ther is much that I do not Know.
You could write a series of match functions do this and then get your average from that. The problem is that match only uses 1 column or row and you have 5 columns to query. To do this use the Min function and then include each of the five match functions in the parenthesis followed by a , to get the shortest skip. The next skips get harder. Now you need an indirect function to adjust the array for the second match. This is a bunch of work to set up. It requires a column for each possible draw number. The match functions consume a lot of memory so get ready for the crawl.
A simpler way to do this is to create a table. For example. Set up your draw history data to include a game number starting with 1 in the first game. You can use the download text file for your game, create a .csv file and add a game number if the download does not include one. Dates can be used; but, the formula is a bit more complicated in order to account for non-game days. You can use this file as a data source from which to lookup values or just paste the values into your worksheet. In this example Cell A6 contains the description “Game#”, Cell B6 “Date”, Cell C6 “Ball 1”….Cell G6 “Ball 5”. Cell H6 is the Power ball. Skip a few columns for other data and start in Cell N6. Enter all of the Balls from 1-49 in the row ending with 49 in Cell BJ6. Now go to Cell N7 and write this formula “IF(COUNTIF($C7:$G7,I$6)=1,$A7,””)”. Copy this formula to all 49 columns and to the last row of your game data. You know have the game numbers that a Ball was Drawn for the Game Block. You are not done. It takes another 49 columns with an equation to subtract the game numbers and get the skips. I use the Large Function for this. With 2000 games The formula for Number 1 in Column N would be “=Large(I$7:I$2000,Row($A1))-Large(I$7:I$2000,Row($A2))”. Copy this formula to each column and to enough rows to get the last game. This packs the values so there are no gaps and shortens the table.
At this point you probably want to export the skips to another workbook so you can turn of this Memory Hog”
Next you can Average.
Sounds like a lot of work; but, it is worth it unless there is a more direct and simpler way than this.
Oh and you have do do something similar for each Power Ball using an = rather than countif function
You could write a series of match functions do this and then get your average from that. The problem is that match only uses 1 column or row and you have 5 columns to query. To do this use the Min function and then include each of the five match functions in the parenthesis followed by a , to get the shortest skip. The next skips get harder. Now you need an indirect function to adjust the array for the second match. This is a bunch of work to set up. It requires a column for each possible draw number. The match functions consume a lot of memory so get ready for the crawl.
A simpler way to do this is to create a table. For example. Set up your draw history data to include a game number starting with 1 in the first game. You can use the download text file for your game, create a .csv file and add a game number if the download does not include one. Dates can be used; but, the formula is a bit more complicated in order to account for non-game days. You can use this file as a data source from which to lookup values or just paste the values into your worksheet. In this example Cell A6 contains the description “Game#”, Cell B6 “Date”, Cell C6 “Ball 1”….Cell G6 “Ball 5”. Cell H6 is the Power ball. Skip a few columns for other data and start in Cell N6. Enter all of the Balls from 1-49 in the row ending with 49 in Cell BJ6. Now go to Cell N7 and write this formula “IF(COUNTIF($C7:$G7,I$6)=1,$A7,””)”. Copy this formula to all 49 columns and to the last row of your game data. You know have the game numbers that a Ball was Drawn for the Game Block. You are not done. It takes another 49 columns with an equation to subtract the game numbers and get the skips. I use the Large Function for this. With 2000 games The formula for Number 1 in Column N would be “=Large(I$7:I$2000,Row($A1))-Large(I$7:I$2000,Row($A2))”. Copy this formula to each column and to enough rows to get the last game. This packs the values so there are no gaps and shortens the table.
At this point you probably want to export the skips to another workbook so you can turn of this Memory Hog”
Next you can Average.
Sounds like a lot of work; but, it is worth it unless there is a more direct and simpler way than this.
Oh and you have do do something similar for each Power Ball using an = rather than countif function
The main part of this is the first Table that lists the game Numbers under the Ball. At this point you could go in another direction and use a macro to copy and paste the values in a second “table”. If you choose this method put an * between the Quotation marks in the formula so that the * is in the non-hit cells. This allows replacing the * with a blank cell and then sorting the column in descending order to get a packed database with no gaps. Then the formula for skips is much simpler.
Hope this Helps.
Hope this Helps.
I'm not sure if that was your question. If you were asking about the average skip of the balls that hit that is a different but simpler setup. To do this you need only 6 columns, 1 for each ball and 1 for the Powerball. The formula for Ball 1 would be "=Min(Match(C7,$C8:$C500,0),Match(C7,$D8:$D500,0),Match(C7,$E8:$E500,0),Match(C7,$F8:$F500,0),Match(C7,$G8:$G500,0))".
Copy this formula to the other 5 Columns and to about 200 games from the bottom of your data. The formula will return an error when there is no match in the Array. This gives you the skip of each ball that hits.
Average here would be for the position not the ball.
That's all I got.
Copy this formula to the other 5 Columns and to about 200 games from the bottom of your data. The formula will return an error when there is no match in the Array. This gives you the skip of each ball that hits.
Average here would be for the position not the ball.
That's all I got.
Last edited:
These formulas worked in my worksheet.
For The Running skip of the Ball drawn in Position 1
=MIN(MATCH(C7,C8:C507,0),MATCH(C7,D8
507,0),MATCH(C7,E8:E507,0),MATCH(C7,F8:F507,0),MATCH(C7,G8:G507,0))
For Digit Skip with first digit in column 1
=LARGE(N7:N506,ROW(A1))-LARGE(N7:N506,ROW(A2))
Errors could come from a couple of things. Sometimes Excel thinks a number is text so large/small functions do not work. Make sure your data is not text. Another thing you can do is multiply the Game Number (A7) by 1 in the formula. Errors might also result if there is no match. You might need to change from 507 to say 3000 to get matches near the bottom of the column. You will get errors at the bottom no matter what. That's because near the bottom the array is loosing data as you go down. To offset this add more data but do not add more calulations or eliminate the lines at the bottom that contain errors..
For The Running skip of the Ball drawn in Position 1
=MIN(MATCH(C7,C8:C507,0),MATCH(C7,D8
507,0),MATCH(C7,E8:E507,0),MATCH(C7,F8:F507,0),MATCH(C7,G8:G507,0))For Digit Skip with first digit in column 1
=LARGE(N7:N506,ROW(A1))-LARGE(N7:N506,ROW(A2))
Errors could come from a couple of things. Sometimes Excel thinks a number is text so large/small functions do not work. Make sure your data is not text. Another thing you can do is multiply the Game Number (A7) by 1 in the formula. Errors might also result if there is no match. You might need to change from 507 to say 3000 to get matches near the bottom of the column. You will get errors at the bottom no matter what. That's because near the bottom the array is loosing data as you go down. To offset this add more data but do not add more calulations or eliminate the lines at the bottom that contain errors..
I downloaded your file. My virus protection went crazy with warnings; but I got the file. There were no formulas to check.
The First thing I noticed was the game numbers need to be in Descending Value order. The Next is the small number of games. If a number does not appear below the Draw in your data, there is no match and you get an error.
I am going to download Power Ball and Set this up for you. It is about time that I learn how to post and share some of my files.
I'll get back to you later today
The First thing I noticed was the game numbers need to be in Descending Value order. The Next is the small number of games. If a number does not appear below the Draw in your data, there is no match and you get an error.
I am going to download Power Ball and Set this up for you. It is about time that I learn how to post and share some of my files.
I'll get back to you later today
I Hope this is what you want. I created about 1000 lines of made up data for a 45 Ball Draw plus a power ball. I assumed that the power ball came from the remaining 40 balls after the 5 Draw.
The file calculates the skips for Each of the 5 Balls from any position and the Power Ball from position 6
Here is the link
http://www.mediafire.com/file/4ljuhyp9m7xaljk/45_Ball_Skips.xlsx
This my first Sharing attempt.
Let me know if it works for you after you insert your own data.
The file calculates the skips for Each of the 5 Balls from any position and the Power Ball from position 6
Here is the link
http://www.mediafire.com/file/4ljuhyp9m7xaljk/45_Ball_Skips.xlsx
This my first Sharing attempt.
Let me know if it works for you after you insert your own data.