Welcome Guest
Log In | Register )
You last visited January 23, 2017, 5:52 pm
All times shown are
Eastern Time (GMT-5:00)

Formula help

Topic closed. 6 replies. Last post 6 years ago by frenchie.

Page 1 of 1
PrintE-mailLink
frenchie's avatar - Lottery-041.jpg
Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
Posted: August 16, 2010, 4:20 am - IP Logged

Hi everyone,I need help for 1 formula, I'm not able yet to transfer link so here I explain.

            This excel file is to keep track of every number's skip ( for the 1000 numbers daily3 ).

And when a number come out ,it start counting from 1 again and up until it hit again and with an ( Index ) formula maybe in column G, I'll be able to see where each number break they cycle, simple chart but I like to see how the numbers move around and see where are they breaking point.

But my problem is that the formula that convert the 3 digit to the whole number does not do a good job because, the number is not reconise by my other formula ??? here is my display.

 

A3    and going down : Draw number.

B3    and going down draw date.

C3    first digit of CA daily3 going down.

D3    second digit.

E3     third digit.

F3     is the (3) digits combine together to create the whole number with formula :    =C3&D3&E3                             maybe not the right one for that ?                   

 

G       empty column for separation.

          And then in :

H1     000

E1      001

J1       002

ETC....   up   to  999.

Then I have to put the number 1 starting cell : H3  all the way to : ALS3  .

Then in cell :

H4  I have this formula :              =IF(ISNA(MATCH(H$1,$F4,0)),H3+1,1)

       drag it all the way to ALS4 and then all the way down to the last draw.

       Here is my problem this formula starting in H4 do not reconise the number in F3 and won't do the job !!?  when I drag it down for all the numbers it does not start at 1 when the number has been hit ??

                 

                         Can someone help to correct it please,          Thank you.

    CARBOB's avatar - FL LOTTERY_LOGO.png
    ORLANDO, FLORIDA
    United States
    Member #4924
    June 3, 2004
    5982 Posts
    Offline
    Posted: August 16, 2010, 6:45 am - IP Logged

    Hi everyone,I need help for 1 formula, I'm not able yet to transfer link so here I explain.

                This excel file is to keep track of every number's skip ( for the 1000 numbers daily3 ).

    And when a number come out ,it start counting from 1 again and up until it hit again and with an ( Index ) formula maybe in column G, I'll be able to see where each number break they cycle, simple chart but I like to see how the numbers move around and see where are they breaking point.

    But my problem is that the formula that convert the 3 digit to the whole number does not do a good job because, the number is not reconise by my other formula ??? here is my display.

     

    A3    and going down : Draw number.

    B3    and going down draw date.

    C3    first digit of CA daily3 going down.

    D3    second digit.

    E3     third digit.

    F3     is the (3) digits combine together to create the whole number with formula :    =C3&D3&E3                             maybe not the right one for that ?                   

     

    G       empty column for separation.

              And then in :

    H1     000

    E1      001

    J1       002

    ETC....   up   to  999.

    Then I have to put the number 1 starting cell : H3  all the way to : ALS3  .

    Then in cell :

    H4  I have this formula :              =IF(ISNA(MATCH(H$1,$F4,0)),H3+1,1)

           drag it all the way to ALS4 and then all the way down to the last draw.

           Here is my problem this formula starting in H4 do not reconise the number in F3 and won't do the job !!?  when I drag it down for all the numbers it does not start at 1 when the number has been hit ??

                     

                             Can someone help to correct it please,          Thank you.

    Enter first formula in cell where you want current skip and drag down,

    Ctrl-Shift-Enter =IF(COLUMNS(AQ19:AQ19)<=COUNTIF($AA$18:$AA$836,$AK19),SMALL(IF($AA$18:$AA$836=$AK19,ROW($AA$18:$AA$836)-ROW($AA$18)+1),COLUMNS(AQ19:AQ19))-1,"")

    enter this formula in cell next to first formula and drag across and down

    Ctrl-Shift-Enter  =IF(COLUMNS($AQ19:AR19)<=COUNTIF($AA$18:$AA$836,$AK19),SMALL(IF($AA$18:$AA$836=$AK19,ROW($AA$18:$AA$836)-ROW($AA$18)+1),COLUMNS($AQ19:AR19))-SUM($AQ19:AQ19)-1,"")

      winsumloosesum's avatar - Lottery-060.jpg
      Pennsylvania
      United States
      Member #2218
      September 1, 2003
      5397 Posts
      Online
      Posted: August 16, 2010, 12:59 pm - IP Logged

      Hi everyone,I need help for 1 formula, I'm not able yet to transfer link so here I explain.

                  This excel file is to keep track of every number's skip ( for the 1000 numbers daily3 ).

      And when a number come out ,it start counting from 1 again and up until it hit again and with an ( Index ) formula maybe in column G, I'll be able to see where each number break they cycle, simple chart but I like to see how the numbers move around and see where are they breaking point.

      But my problem is that the formula that convert the 3 digit to the whole number does not do a good job because, the number is not reconise by my other formula ??? here is my display.

       

      A3    and going down : Draw number.

      B3    and going down draw date.

      C3    first digit of CA daily3 going down.

      D3    second digit.

      E3     third digit.

      F3     is the (3) digits combine together to create the whole number with formula :    =C3&D3&E3                             maybe not the right one for that ?                   

       

      G       empty column for separation.

                And then in :

      H1     000

      E1      001

      J1       002

      ETC....   up   to  999.

      Then I have to put the number 1 starting cell : H3  all the way to : ALS3  .

      Then in cell :

      H4  I have this formula :              =IF(ISNA(MATCH(H$1,$F4,0)),H3+1,1)

             drag it all the way to ALS4 and then all the way down to the last draw.

             Here is my problem this formula starting in H4 do not reconise the number in F3 and won't do the job !!?  when I drag it down for all the numbers it does not start at 1 when the number has been hit ??

                       

                               Can someone help to correct it please,          Thank you.

      Frenchie,

      Here is a download link to zip file that contains California Pick 3 Draws.

      The 1st tab is where you click the specific toolbar to add additional draws.  Midday, Evening, and Combined.  All you do is just click the Toolbar and go to the Tab M = Midday, E = Evening, or C = Combined then enter the new drawing.  All draw history has been updated through Sunday August 15, 2010.

      There 3 additional Tabs for Midday, Evening, and Combined that will give you the current hits and skips for all 1000 Pick 3 Combinations.

      Hope this helps!!

      Here is the download link:

      http://www.box.net/shared/pbv6g230hb

        frenchie's avatar - Lottery-041.jpg
        Los Angeles
        United States
        Member #75410
        June 2, 2009
        489 Posts
        Offline
        Posted: August 16, 2010, 8:35 pm - IP Logged

        CARBOB,

                   Thank you very much for your quick respond, but it doesn't fit my excel file that I explain in the fisrt post!!!

        or I'm the one who don't understand ? I just need a formula for my column F.

                   What I do is that, I download the draw history from CA daily3 evening website which is in " Text Document " and then tranfer it into my excel spread sheet, and I think that's where the problem is?

                   when it is transfer into excel it doesn't tranfer as number but under something else, and I went to the " Number Format " window to change it but nothing works !!! It ONLY work when I type the number over with my keybord.

        So what I need is to have a formula that will tranfer those 3 digits into a number as a number,I think that what it is ?

                                                                                                                         Thank you CARBOB...

          frenchie's avatar - Lottery-041.jpg
          Los Angeles
          United States
          Member #75410
          June 2, 2009
          489 Posts
          Offline
          Posted: August 16, 2010, 10:57 pm - IP Logged

          winsumloosesum,

          Thank you very much for your file it's very interesting.

          Question : can this same file be done for French Keno 20/70 ?          Thank you.

            CARBOB's avatar - FL LOTTERY_LOGO.png
            ORLANDO, FLORIDA
            United States
            Member #4924
            June 3, 2004
            5982 Posts
            Offline
            Posted: August 17, 2010, 4:36 am - IP Logged

            type a 1 in an empty cell, copy that cell, goto F3, paste special, multiply. Then format col F , Custom, type in 000. See if that works.

              frenchie's avatar - Lottery-041.jpg
              Los Angeles
              United States
              Member #75410
              June 2, 2009
              489 Posts
              Offline
              Posted: August 17, 2010, 6:35 am - IP Logged

              CARBOB,

                               Thank you ,it works I will keep that trick in mind for the next time,Thank you for your help,I realy appreciate it.

                                                                                                Regards...                         Serge.