Welcome Guest
Log In | Register )
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
PrintE-mailLink
CARBOB's avatar - FL LOTTERY_LOGO.png
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.

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

    =SUBSTITUTE(ADDRESS(1,(D3-1)/50+1,4),"1","")

    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!

      Avatar
      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.

        Avatar
        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.

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

          "Hits"