Welcome Guest
You last visited December 6, 2016, 5:30 am
All times shown are
Eastern Time (GMT-5:00)

# Looking for Excel Formula

Topic closed. 19 replies. Last post 1 year ago by AllenB.

 Page 2 of 2
ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5896 Posts
Offline
 Posted: August 21, 2015, 1:34 pm - IP Logged

The results are too unevenly split, not what I was looking for.

New Jersey
United States
Member #17843
June 28, 2005
49699 Posts
Offline
 Posted: August 21, 2015, 2:27 pm - IP Logged

This formula puts everything in a group of 50.

Thanks for responding, I'm always open to food for thought when it comes to Excel and Tracking Lottery Results.

A mind once stretched by a new idea never returns to its original dimensions!

Lincoln, California
United States
Member #167130
June 27, 2015
256 Posts
Offline
 Posted: August 21, 2015, 3:22 pm - IP Logged

Depending on Your data source there are several ways to do this.  For CA Fantasy 5 I have used this approach.

Dates (A),  Game# (B) and Picks (C-G) are listed in the first 6 columns in the second row.

in the next 39 (H-AT) columns (top row) enter 1 - 39

under each number enter the following formula. =if(countif(\$C3:\$G3,H\$1)=1,+\$B3,-1)

This formula places the game number in the cell if the number in line 1 was in the set drawn and -1 if it is not.

Copy the formula in all of the cells under the 1-39 numbers for as many games as you want to go back.

To Process this Copy Columns H-AT onto a separate worksheet in the work book and paste as values.

You can then sort each column in descending order and get rid of all of the -1 values with find and replace.

Highlight all of the cells in this new block that contains all of the numbers and games and name it "_Game<snip>" or anything you want.

Now you have the ability to access this data using a vlookup function

For example:  For number 1  The formula =vlookup(1,_Game<snip>,2) would give you the game# at the second cell under 1.  You can create a list of the hits for all of the numbers by using a cell addresses  instead of 1 and 2.

For example: to look again at 1  Enter 1 in the cell at the top of the column you want to place the lookup values. I you do this on s separate worksheet in the workbook you could enter 1-39 in cells (B1:AN1).  In Column A starting in Row 2 enter 1-20 in increasing order down the column ( enter more if you want to look back farther.  In cell B2 inter the formula =vlookup(B\$1,__Game<snip>,\$A2)-vlookup(B\$1,_Game<snip>,\$A3).

This gives you the Skip between the times the number is picked.  You can then use a Filter on the Column to select any value or calculated value using the filters criteria.

If the vlookup stuff throws you. Insert a column between each number in the new block next to the first game under a number enter a formula to add te adjacent cell minus the next cell down to get the skips between picks. You can then use filters with values and ranges of values to select the the skips in the column

This probably seams like a lot of work; but once you do it it is easy to update with macros and the data can be used in any table or formula you want.  Options are many

Good Luck.  I can't say I did not have any typos in the formulas above so if it does not work let me know and I will help.  I have been doing this with fantasy 5 for a while and could give you some other examples of how I use the data if you are interested.

This post has been automatically changed by the Lottery Post computer system to remove inappropriate content and/or spam.

Lincoln, California
United States
Member #167130
June 27, 2015
256 Posts
Offline
 Posted: August 21, 2015, 3:24 pm - IP Logged

I don't know where the <Snip> came from.  i was using Game<snip> for the range name

This post has been automatically changed by the Lottery Post computer system to remove inappropriate content and/or spam.

Lincoln, California
United States
Member #167130
June 27, 2015
256 Posts
Offline
 Posted: August 21, 2015, 3:25 pm - IP Logged

"Hits"

 Page 2 of 2