Welcome Guest
Log In | Register )
You last visited December 6, 2016, 11:05 am
All times shown are
Eastern Time (GMT-5:00)

help with Excel!

Topic closed. 9 replies. Last post 8 years ago by jm6256.

Page 1 of 1
PrintE-mailLink
Avatar
New Member
New York
United States
Member #7355
September 30, 2004
19 Posts
Offline
Posted: May 3, 2009, 1:45 am - IP Logged

can anyone help me with 2 problems

1) if cell A has 638  which formula can I use to sort it (368)

2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

thanks

Jim

    Avatar
    New Mexico
    United States
    Member #58526
    February 18, 2008
    683 Posts
    Offline
    Posted: May 3, 2009, 3:18 am - IP Logged

    can anyone help me with 2 problems

    1) if cell A has 638  which formula can I use to sort it (368)

    2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

    thanks

    Jim

    Hi, jm, let's see if I can point you in the right direction....

    1) if cell A has 638  which formula can I use to sort it (368)

    You have to break the # (638) down to it's individual numbers and then sort them small to large (at least that's what it sounds like you want)

    -the MID function in excel can select individual characters from a cell, ie. MID(A1,1,1)......would return the 1st character from cell A1 and it would only return that one where MID(A1,2,2) would return the characters starting at character two and return the 3 and the 8

    -the LARGE or SMALL function can be used to sort numbers

     

    Let's say cell A1 has the 638

    in cell B1 enter the formula =MID(A1,1,1)        [should return a 6]

    in cell C1 enter the formula =MID(A1,2,1)        [should return a 3]

    in cell D1 enter the formula =MID(A1,3,1)        [should return a 8]

     

    then in cell E1 enter the formula =SMALL(B1:D1,1)&SMALL(B1:D1,2)&SMALL(B1:D1,3)     [should return 368]

     

    hope this helps!!!!

      Avatar
      New Mexico
      United States
      Member #58526
      February 18, 2008
      683 Posts
      Offline
      Posted: May 3, 2009, 3:20 am - IP Logged

      can anyone help me with 2 problems

      1) if cell A has 638  which formula can I use to sort it (368)

      2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

      thanks

      Jim

      2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

       

      I use the MATCH function to look through a column of numbers and match what I lookin' for......look in Excel's help files for the MATCH function and it should help you out

        Avatar
        New Member
        New York
        United States
        Member #7355
        September 30, 2004
        19 Posts
        Offline
        Posted: May 3, 2009, 7:28 am - IP Logged

        2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

         

        I use the MATCH function to look through a column of numbers and match what I lookin' for......look in Excel's help files for the MATCH function and it should help you out

        thanks Texas MadMan i'll give it a try

          Avatar

          United States
          Member #41846
          June 23, 2006
          459 Posts
          Offline
          Posted: May 3, 2009, 8:18 am - IP Logged

          JM

          am I correct in that you want to find any previous box match for a specific #, and that changing the order from low to high was just a step in the process?   I do this with a VBA if you want to consider a vba solution

          p8

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

            can anyone help me with 2 problems

            1) if cell A has 638  which formula can I use to sort it (368)

            2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

            thanks

            Jim

            Answer question #2

            =MATCH($A1,INDIRECT("K20:K120"),0)-0

            Ok,  if you have the combination 368 in cell A1 and all your past results in cell range K20 through K120 (see formula above).  You can increase K120 depending how many lines of past history draws.  For example you could have a cell range of K20:K5000.  The "K" = cell range is where you have your 3 digit combination results.

             

            Answer to question #1

            Depending on what version of Excel you are using.  I am using 2002.

            I use a free add-in for Excel called Morefunc.

            http://xcell05.free.fr/morefunc/english/

            (Should be at the bottom of the page)

            Follow the instructions for using this add-in. 

            Open Excel and go to Tools > Addins and make sure Morefunc has a checkmark next to it.

            This is the formula I use for changing a straight combination into a boxed version

            --MCONCAT(VSORT(MID(E20,INTVECTOR(LEN(E20),1),1)+0,,1))

            Whwn you enter the above formula you will need to use Ctrl - Shift - Enter on your keyboard.

            In the above formula E20 is where the actual drawing combination is located.  In cell D20 I entered the above formula to give me the ascending boxed form.

            For example.  If E20 was 931 then in D20 the result would be 139.

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

              Answer question #2

              =MATCH($A1,INDIRECT("K20:K120"),0)-0

              Ok,  if you have the combination 368 in cell A1 and all your past results in cell range K20 through K120 (see formula above).  You can increase K120 depending how many lines of past history draws.  For example you could have a cell range of K20:K5000.  The "K" = cell range is where you have your 3 digit combination results.

               

              Answer to question #1

              Depending on what version of Excel you are using.  I am using 2002.

              I use a free add-in for Excel called Morefunc.

              http://xcell05.free.fr/morefunc/english/

              (Should be at the bottom of the page)

              Follow the instructions for using this add-in. 

              Open Excel and go to Tools > Addins and make sure Morefunc has a checkmark next to it.

              This is the formula I use for changing a straight combination into a boxed version

              --MCONCAT(VSORT(MID(E20,INTVECTOR(LEN(E20),1),1)+0,,1))

              Whwn you enter the above formula you will need to use Ctrl - Shift - Enter on your keyboard.

              In the above formula E20 is where the actual drawing combination is located.  In cell D20 I entered the above formula to give me the ascending boxed form.

              For example.  If E20 was 931 then in D20 the result would be 139.

              I should have added this in the previous post.  When you are using the Match formula for finding how many drawings since a combination hit, the result from the formula may reult in a "NA" because of the limited number of past drawings in your history range.  Especialy straight combinations.  Some states may have a straight combination out 6000 or more draws.  For boxed combinations you would need probably a smaller history.

              You could use this formula in cell B1:

              =IF(ISNA(MATCH(A$1,INDIRECT("K20:X120"),0)-0),100,MATCH(A$1,INDIRECT("K20:K120"),0)-0)

              K20:K120 would mean that you only have 119 past draws in your history.  Of course this would be too small.  You can change the formula above.

              What the above formula will do is try to match the combination in cell A1 with the the results in cell range K20:K12.  If the combination is NOT found instead of a "NA" in cell B1 the formula will enter a 100 in cell B1 because you only have 100 past draws to calculate.  The more history you have, you can increase The K120 and also change the "100" in the formula above.  At least this will eliminate the "NA"'s

                Avatar
                New Member
                New York
                United States
                Member #7355
                September 30, 2004
                19 Posts
                Offline
                Posted: May 3, 2009, 11:36 am - IP Logged

                I should have added this in the previous post.  When you are using the Match formula for finding how many drawings since a combination hit, the result from the formula may reult in a "NA" because of the limited number of past drawings in your history range.  Especialy straight combinations.  Some states may have a straight combination out 6000 or more draws.  For boxed combinations you would need probably a smaller history.

                You could use this formula in cell B1:

                =IF(ISNA(MATCH(A$1,INDIRECT("K20:X120"),0)-0),100,MATCH(A$1,INDIRECT("K20:K120"),0)-0)

                K20:K120 would mean that you only have 119 past draws in your history.  Of course this would be too small.  You can change the formula above.

                What the above formula will do is try to match the combination in cell A1 with the the results in cell range K20:K12.  If the combination is NOT found instead of a "NA" in cell B1 the formula will enter a 100 in cell B1 because you only have 100 past draws to calculate.  The more history you have, you can increase The K120 and also change the "100" in the formula above.  At least this will eliminate the "NA"'s

                thanks winsumloosesum, I have history from 2002. which is why I was looking for a way to look into numbers of days since a number was drawn (probably will use it for box and double) for now.

                  Avatar

                  United States
                  Member #41846
                  June 23, 2006
                  459 Posts
                  Offline
                  Posted: May 3, 2009, 1:21 pm - IP Logged

                  JM

                  heres a vba solution. put your numbers to be checked in row 1 col's EFG all boxed or str comb will be displayed startin in row 4 then exact order will be displayed starting in col E

                  I always recommend you copy your data to a new name, then close the original and work with the copy. if you don't like the results just delete and all is well.

                  I apologise if you already know all of this, but untill recently I could not comprehend these first few steps and wasted years continuing to work in DOS and GWBasic

                  with spreadsheet open press alt F8. type the name you want to use for this program in line below macro name. lets assume test then click on create. you will now have a window with the following lines

                  Sub test()

                  End Sub

                  your code goes between these lines. copy the code below and paste into this space in your spreadsheet. click on the red x and your back to your spreadsheet.

                  to run the file press alt F8 then select the file name if not already selected then click run and program executes. if you want to change some part of the program press alt F11 and you can see the code. as long as you are in your copy, change any thing you want to see what effect it has. always save you data.

                  good luck

                  p8

                   

                   

                   

                  ' Sub prev_box_hits() name i used

                  ' target numbers in col a assumed compressed as 123 not 1 2 3

                  ' steps

                  ' dim some variables

                  ' set up loops

                  'decompress #'s

                  ' store target number in array

                  ' place next # to be tested in a different array

                  ' compare the two arrays if all #'s match then it is a box match

                  ' subtract target # location (row #) from # to be tested row # put difference in col c or your choice

                  '

                  mt = 4 'start matches at row 4

                  mx = 4000 ' change to the # of records your database has+a few hundred

                  Dim tn(9), tar(9), dn(4000, 3) ' change to match variable mx

                  ' tn=test # tar=target # dn decompressed #'s mx sets of 3 #'s

                  GoSub decompress 'convert to single # and store in memory for fast access

                  ' all the digits are now in dn(x,y)

                  'For x = 1 To mx: For y = 1 To 3: Cells(x, y + 4) = dn(x, y): Next y: Next x

                  'use above line if you want to verify #'s were converted corectly

                   

                  'numbers to be tested in row 1 col 5,6,7or E,F,G

                  For Z = 0 To 9: tar(Z) = 0: Next Z 'zero out array

                  For y = 5 To 7: a = Cells(1, y)

                  tar(a) = tar(a) + 1

                  Next y ' you have loaded array tar() with a count for each target #

                  For y = 0 To 9: Cells(3, y + 2) = tar(y): Next y

                  For w = 1 To mx 'loop thru remaining data look for match

                  For Z = 0 To 9: tn(Z) = 0: Next Z ' zero this array before every test

                  For y = 1 To 3: a = dn(w, y)

                  tn(a) = tn(a) + 1

                  Next y ' you have loaded array tn() with a count for each target #

                  GoSub compare ' you could do this in line i think its easier to follow this way

                  Next w ' complete this loop

                   

                   

                   

                   

                   

                  Exit Sub 'exit this program all below is called from above

                  decompress:

                  For x = 1 To mx

                  Z = Cells(x, 1) ' x is row, 1 is col change if data in different col

                  a = Mid(Z, 1, 1) 'first character

                  b = Mid(Z, 2, 1) ' normally i would put in a loop this is easier to understand

                  c = Mid(Z, 3, 1)

                  If a = "" Then mx = x - 1: GoTo done 'out of data adjust mx value then exit

                  dn(x, 1) = a: dn(x, 2) = b: dn(x, 3) = c

                  Next x

                   

                   

                  done:

                  Return

                  compare: ' compare the two arrays

                  m = 0

                  For Z = 0 To 9

                  If tn(Z) = tar(Z) Then m = m + 1

                  Next Z

                  If m = 10 Then

                  Cells(mt, 3) = w ' box or str match in previous game # display in col C

                  For y = 1 To 3: Cells(mt, y + 4) = dn(w, y)

                  Next y

                  mt = mt + 1

                  End If

                  Return

                  'End Sub only want one

                    Avatar
                    New Member
                    New York
                    United States
                    Member #7355
                    September 30, 2004
                    19 Posts
                    Offline
                    Posted: May 3, 2009, 9:56 pm - IP Logged

                    thanks for the help  P8!