Welcome Guest
Log In | Register )
You last visited December 4, 2016, 9:25 pm
All times shown are
Eastern Time (GMT-5:00)

Excel FYI

Topic closed. 33 replies. Last post 11 years ago by hypersoniq.

Page 2 of 3
PrintE-mailLink
lottaloot's avatar - AvatarZ56
Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
Posted: December 8, 2005, 11:50 pm - IP Logged

Here's a helpful formula for changing sums into root form.  I figured this one out all by my little self.  Big Smile

=IF(AB2>9,RIGHT(AB2,1)+LEFT(AB2,1),AB2)

It works well except for the sum of 19 (it changes that into 10 instead of 1)  Can't figure that one out yet. 

I am pretty sure that there should be an (or) function in there somewhere. 

L ttaL   T

    winsumloosesum's avatar - Lottery-060.jpg
    Pennsylvania
    United States
    Member #2218
    September 1, 2003
    5387 Posts
    Offline
    Posted: December 9, 2005, 10:37 am - IP Logged

    lottaloot,

    This might help.

    If you create a worksheet and name it Sums

    List all the Sums from 0 through 27 in column A
    List all the Roots in column B

    A             B

    0             0
    1     1
    2     2
    3     3
    4     4
    5     5
    6     6
    7     7
    8     8
    9     9
    10     1
    11     2
    12     3
    13     4
    14     5
    15     6
    16     7
    17     8
    18     9
    19     0
    20     2
    21     3
    22     4
    23     5
    24     6
    25     7
    26     8
    27     9


    The worksheet with all your drawings.

    My worksheet has the total sums in cell column V

    My root sum is in column W with this formula: =LOOKUP(V2,Sums!$A$1:$A$28,Sums!$B$1:$B$28)

    So V2 is where I have my most recent entry.

    You can also add the other type of Sum (27 = 7) in the Sums worksheet.

    0     0
    1     1
    2     2
    3     3
    4     4
    5     5
    6     6
    7     7
    8     8
    9     9
    10     0
    11     1
    12     2
    13     3
    14     4
    15     5
    16     6
    17     7
    18     8
    19     9
    20     0
    21     1
    22     2
    23     3
    24     4
    25     5
    26     6
    27     7

      cps10's avatar - Lottery-004.jpg
      The Carolinas - Charlotte
      United States
      Member #21627
      September 12, 2005
      4138 Posts
      Offline
      Posted: December 9, 2005, 10:47 am - IP Logged

      This was one I tried about 3 months ago and was curious about: how to find out the number of draws out with one particular number. For example, if 134 drew on 6/7/05 and then the 1 didn't draw again until 6/16/05, that would be 9 draws the 1 was out. Any formula for that?

        winsumloosesum's avatar - Lottery-060.jpg
        Pennsylvania
        United States
        Member #2218
        September 1, 2003
        5387 Posts
        Offline
        Posted: December 9, 2005, 11:26 am - IP Logged

        CPS,

         

        My Excel drawings are in descending order.  Meaning, I have the most recent drawing at the top.  So my most recent drawing is on row 10.

        A10 = Date

        B10 = Pick 3 Number Drawn

        C10 = Position 1

        D10 = Position 2

        E10 = Position 3

        My worksheet name is PA3M

        I have a second worksheet name called: Charts

        It has a chart like below:

        The "P1" is in cell B1, "P2" is in cell C1 etc

        The "0" is in cell A2, "1" in A3 etc.

         P1P2P3
        0   
        1   
        2   
        3   
        4   
        5   
        6   
        7   
        8   
        9   


         

        So in cell B2 I have the folowing formula:

        =MATCH($A2,INDIRECT("PA3M!c10:c100"),0)-1

        So what this formula is doing is matching the "0" in cell A2 and then going to the worksheet "PA3M" in cell range C10 through C100 and finding how many drawings it's been since the "0" Zero hit in Position 1.

        So if in my most recent drawnig in Position 1 a "0" was drawn, a 0 would be in cell B2. 

         

         

         

          cps10's avatar - Lottery-004.jpg
          The Carolinas - Charlotte
          United States
          Member #21627
          September 12, 2005
          4138 Posts
          Offline
          Posted: December 9, 2005, 11:34 am - IP Logged

          Thanks winsumloosesum I will try that one out.

            retxx's avatar - mrthumbs
            BOSTON
            United States
            Member #48
            September 9, 2001
            3584 Posts
            Offline
            Posted: December 9, 2005, 10:25 pm - IP Logged

            winsum could you post a link get the excel chart and will it show the past due pairs to play that are due? thanks

              lottaloot's avatar - AvatarZ56
              Redford/MI
              United States
              Member #3396
              January 18, 2004
              4867 Posts
              Offline
              Posted: December 10, 2005, 8:09 am - IP Logged

              lottaloot,

              This might help.

              If you create a worksheet and name it Sums

              List all the Sums from 0 through 27 in column A
              List all the Roots in column B

              A             B

              0             0
              1     1
              2     2
              3     3
              4     4
              5     5
              6     6
              7     7
              8     8
              9     9
              10     1
              11     2
              12     3
              13     4
              14     5
              15     6
              16     7
              17     8
              18     9
              19     0
              20     2
              21     3
              22     4
              23     5
              24     6
              25     7
              26     8
              27     9


              The worksheet with all your drawings.

              My worksheet has the total sums in cell column V

              My root sum is in column W with this formula: =LOOKUP(V2,Sums!$A$1:$A$28,Sums!$B$1:$B$28)

              So V2 is where I have my most recent entry.

              You can also add the other type of Sum (27 = 7) in the Sums worksheet.

              0     0
              1     1
              2     2
              3     3
              4     4
              5     5
              6     6
              7     7
              8     8
              9     9
              10     0
              11     1
              12     2
              13     3
              14     4
              15     5
              16     6
              17     7
              18     8
              19     9
              20     0
              21     1
              22     2
              23     3
              24     4
              25     5
              26     6
              27     7

              Thanks for the above suggestion. 

              WSLS, for my LDR, I have the following formula in column BN.  Column BM has my sums listed.   

              =RIGHT(BM2,1)*1

              L ttaL   T

                lottaloot's avatar - AvatarZ56
                Redford/MI
                United States
                Member #3396
                January 18, 2004
                4867 Posts
                Offline
                Posted: December 10, 2005, 8:15 am - IP Logged

                CPS,

                 

                My Excel drawings are in descending order.  Meaning, I have the most recent drawing at the top.  So my most recent drawing is on row 10.

                A10 = Date

                B10 = Pick 3 Number Drawn

                C10 = Position 1

                D10 = Position 2

                E10 = Position 3

                My worksheet name is PA3M

                I have a second worksheet name called: Charts

                It has a chart like below:

                The "P1" is in cell B1, "P2" is in cell C1 etc

                The "0" is in cell A2, "1" in A3 etc.

                 P1P2P3
                0   
                1   
                2   
                3   
                4   
                5   
                6   
                7   
                8   
                9   


                 

                So in cell B2 I have the folowing formula:

                =MATCH($A2,INDIRECT("PA3M!c10:c100"),0)-1

                So what this formula is doing is matching the "0" in cell A2 and then going to the worksheet "PA3M" in cell range C10 through C100 and finding how many drawings it's been since the "0" Zero hit in Position 1.

                So if in my most recent drawnig in Position 1 a "0" was drawn, a 0 would be in cell B2. 

                 

                 

                 

                CPS10, I believe that you could you also change this formula to where it would search for the zero in any position using this formula.   

                =MATCH($A2,INDIRECT("PA3M!c10:e100"),0)-1

                 

                I am also getting the feeling that you can do more searching with less formulas if you add names to your sheet.

                L ttaL   T

                  lastexit51's avatar - scene sunoverlake.jpg
                  New Member
                  tn
                  United States
                  Member #19631
                  August 3, 2005
                  16 Posts
                  Offline
                  Posted: December 10, 2005, 8:58 am - IP Logged

                  Is anybody running excel on a celeron processor? I want to get it but I read on the package that you have to have a pentium processor. The guy who fixed my computer said he thoght it would work but i would like another opinion.

                    powerplayer's avatar - Lottery-022.jpg

                    United States
                    Member #17834
                    June 28, 2005
                    2083 Posts
                    Offline
                    Posted: December 10, 2005, 10:01 am - IP Logged

                    Is anybody running excel on a celeron processor? I want to get it but I read on the package that you have to have a pentium processor. The guy who fixed my computer said he thoght it would work but i would like another opinion.

                    HI lastexit51,

                    Yes it's fine to run it on a celeron processor.

                    They are made by Intel but, it's a cheaper model then the real pentium.

                    Powerplayer

                    Good luck to everyone!!!

                      hypersoniq's avatar - 8ball
                      Pennsylvania
                      United States
                      Member #1340
                      April 6, 2003
                      2450 Posts
                      Offline
                      Posted: December 10, 2005, 2:40 pm - IP Logged

                      I like the SUMPRODUCT formula.

                      Another great timesaver (and one that aids immensely in future readability) is the use of 'Named Ranges'... replace all of that $A$2:$A$9 with something like "number_1".

                      here is one from my latest system project...

                      =SUMPRODUCT(--(number_1=$A2),--(announcer_1=B$1))

                      this creates a grid where column A has numbers drawn (0-9) (A2=0, A11=9)

                      Row 1 of columns B:K are 0-9 (announcer numbers)

                      the grid located in B2:K11 contains the above formula (above is in B2) and was simply autofilled to create the grid

                      number_1 is off of another worksheet (see how easy named ranges are?) and represents the first digit drawn in the ENTIRE pa lottery evening game history (all 9,864 draws from 3/1/1977 to 12/9/2005)

                      announcer_1 is simply the numbers column copied and pasted to a new column one row lower (so I can see what number followed every number)

                      on the announcer worksheet I have 3 such grids, one for each position.

                      precursor_1 is the number drawn BEFORE the draw in question (same draw history, shifted a row UP), and that is the basis for this newest system, qualifying the announcer by frequency of the precursor, in each position... more to come on that later...

                      I also like the --, that's one I didn't know about before now.

                      thanks JKING for the formula and thanks CARBOB for the 411 on --

                      Playing more than one ticket per game is betting against yourself.

                        hypersoniq's avatar - 8ball
                        Pennsylvania
                        United States
                        Member #1340
                        April 6, 2003
                        2450 Posts
                        Offline
                        Posted: December 10, 2005, 9:06 pm - IP Logged

                        Also, to up the info ante...

                        you can define what are called "Dynamic Named Ranges",or ranges that grow to accomodate new draws without re-typing or editing the formulas.

                        read about it here... http://www.beyondtechnology.com/geeks007.shtml

                        I use that in my pick3 sheet for all of PA's data, and the newly created pick4 sheet. it looks like this (entered in the "Refers to:" box when defining the named range)...

                        =OFFSET(sbdraws!$D$1,3,0,(COUNTA(sbdraws!$D:$D)-2),1)

                        another quick tip

                        If you have calculations, such as

                        1. separating combos into draws

                        2. converting to v-tracs or other filters

                        3. any other operations that use formulas that output static numbers

                        select the range where all of the formulas are (except for the most recent row, so you don't lose your formulas)...click COPY... then go to edit-paste special... then choose VALUES. this replaces the autofilled formulas with the numeric results.

                        what good is that, you ask?

                        before doing that, my pick 3 file was 3.45MB, after doing that, 1.3MB. it loads faster and recalculates quicker too.

                        Playing more than one ticket per game is betting against yourself.

                          Avatar
                          Anna, TX
                          United States
                          Member #26720
                          November 21, 2005
                          263 Posts
                          Offline
                          Posted: December 10, 2005, 10:01 pm - IP Logged

                          hey I have a question. I am working on my high/ low filters..

                          I have a seperate sheet dedicated for it..anyway...

                              a                        b                c        d        e          f              g              h              i                j                 and so on...       

                            date                  pick3            an1  an2    an3      lth            rth            side          h/l            h/l              h/l

                          11/21/05

                          581

                          5

                          8

                          1

                          58

                          81

                          51

                          H

                           

                           

                           

                           

                           

                           

                          11/22/05

                          4

                          0

                          0

                          4

                          0

                          4

                          4

                           

                           

                           

                           

                           

                          0

                          L

                          11/23/05

                          198

                          1

                          9

                          8

                          19

                          98

                          18

                           

                           

                           

                           

                           

                          1

                          L

                          11/24/05

                          929

                          9

                          2

                          9

                          92

                          29

                          99

                           

                           

                           

                           

                           

                          2

                          L

                          11/25/05

                          394

                          3

                          9

                          4

                          39

                          94

                          34

                           

                           

                           

                           

                           

                          3

                          L

                          11/26/05

                          752

                          7

                          5

                          2

                          75

                          52

                          72

                           

                           

                           

                           

                           

                          4

                          L

                          11/27/05

                          na

                            

                           

                           

                           

                           

                           

                          5

                          H

                          11/28/05

                          842

                          8

                          4

                          2

                          84

                          42

                          82

                           

                           

                           

                           

                           

                          6

                          H

                          11/29/05

                          426

                          4

                          2

                          6

                          42

                          26

                          46

                           

                           

                           

                           

                           

                          7

                          H

                          11/30/05

                          345

                          3

                          4

                          5

                          34

                          45

                          35

                           

                           

                           

                           

                           

                          8

                          H

                          12/01/05

                          na

                             

                           

                           

                           

                           

                           

                          9

                          H

                          12/01/05

                          94

                          0

                          9

                          4

                          9

                          94

                          4

                           

                           

                           

                           

                           

                           

                           

                          12/02/05

                          874

                          8

                          7

                          4

                          87

                          74

                          84

                           

                           

                           

                           

                           

                           

                           

                          12/03/05

                          623

                          6

                          2

                          3

                          62

                          23

                          63

                           

                           

                           

                           

                           

                           

                           

                          12/05/05

                          468

                          4

                          6

                          8

                          46

                          68

                          48

                           

                           

                           

                           

                           

                           

                           

                          12/06/05

                          126

                          1

                          2

                          6

                          12

                          26

                          16

                           

                           

                           

                           

                           

                           

                           

                          12/07/05

                          219

                          2

                          1

                          9

                          21

                          19

                          29

                           

                           

                           

                           

                           

                           

                           

                          12/08/05

                          878

                          8

                          7

                          8

                          87

                          78

                          88

                           

                           

                           

                           

                           

                           

                           

                          12/09/05

                          786

                          7

                          8

                          6

                          78

                          86

                          76

                           

                           

                           

                           

                           

                           

                           

                           

                          formulas in lth, rth and side have this formula (well changes for each cell, you know what I mean)

                          =INT((D3+(C3*100/10)))

                           

                          now I want the h/l rows to display either hh, hL, Lh, LL and not just one H or Lwhich is all I have been able to do so far...

                           

                          I hope this displays right and I made sense. I can forward my spreadsheet if you want to get a better understadning

                            Avatar
                            Anna, TX
                            United States
                            Member #26720
                            November 21, 2005
                            263 Posts
                            Offline
                            Posted: December 10, 2005, 11:02 pm - IP Logged

                            nope, didn't post right...sigh

                              hypersoniq's avatar - 8ball
                              Pennsylvania
                              United States
                              Member #1340
                              April 6, 2003
                              2450 Posts
                              Offline
                              Posted: December 11, 2005, 2:02 pm - IP Logged

                              here is my latest evolution of this formula, inserting a logical combination of a draw with the draw before it, then scaning the history to count the announcer frequency...

                              =SUMPRODUCT(--((precursor_3*10)+number_3=$A2),--(announcer_3=B$1))

                              where column A is a list of combinations from 00 to 99, row 1 of columns B:K are 0-9

                              that formula tells me a full count (for one position) of how many times the numbers 0-9 appeared as announcers with any given precursor+draw pair

                              I have 3 worksheets like this, one for each position.

                              I can use it like this...

                              if last night's first position number was 4 and the night before was 8, this becomes 84, all I have to do is scroll down to 84 in column A to see the distribution of ALL the announcer digits

                              I can see that the most frequent follower of 84 is 0, which happened 16 times. pure announcer data (without precursor info) shows 3 as the most frequent follower of 4 in position one.

                              now all I have to do is figure out the next step of the "system"

                               

                              Playing more than one ticket per game is betting against yourself.