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

Excel Help!!

Topic closed. 13 replies. Last post 11 years ago by lottaloot.

Page 1 of 1
PrintE-mailLink
winsumloosesum's avatar - Lottery-060.jpg
Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Online
Posted: June 17, 2005, 6:31 pm - IP Logged

Excel professionals.

I am trying to find a formula for taking a Pick 3 and Pick 4 from its drawn combination and formulate it to the ascending order.

Example:

Pick 3  Cell A1  748 

Formula to change it to: 478 in cell B1

 

Pick 4  Cell A1  7482

Formula to change it to: 2478 in cell B1

 

Thanks in advance!!

wsls

 

    lottaloot's avatar - AvatarZ56
    Redford/MI
    United States
    Member #3396
    January 18, 2004
    4867 Posts
    Offline
    Posted: June 17, 2005, 10:29 pm - IP Logged

    I am going to give this a whirl until SirMetro to the rescue. 

    Example:

    Pick 3  Cell A1  748 

    Formula to change it to: 478 in cell B1

    If the draw results were

    B1=7

    C1=4

    D1=8

    E1 =CONCATENATE(C1,B1,D1)  Results would be 478

    Still trying to get the change to occur within the next cell by using the =SUM( LT)  =SUM (MID) functions

      SirMetro's avatar - center
      East of Atlanta
      United States
      Member #6191
      August 11, 2004
      1389 Posts
      Offline
      Posted: June 17, 2005, 10:41 pm - IP Logged

      Grrrr....I have been avoiding doing that one for a bit...guess I had better think bout it for a bit cause I could use it as well.

      Sir Metro

        LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
        Tx
        United States
        Member #4570
        May 4, 2004
        5180 Posts
        Offline
        Posted: June 17, 2005, 11:07 pm - IP Logged

        Can you all read minds?

        I have been needing to do that to a list (single vertical column) of pick 3 numbers (combos) for a long long time and with no software to do it with (among other things that I have also been needing to do, but can't remember what they are just now, because I gave up on being able to do them time ago)

        BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

        "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

          lottaloot's avatar - AvatarZ56
          Redford/MI
          United States
          Member #3396
          January 18, 2004
          4867 Posts
          Offline
          Posted: June 18, 2005, 3:04 am - IP Logged

          Can anyone tell me what the function offset does?  In layman's terms.  I get easily Confused

            Bluegrass's avatar - nw archer.jpg
            Killer's Cove
            United States
            Member #57
            December 17, 2001
            5625 Posts
            Offline
            Posted: June 18, 2005, 4:37 am - IP Logged

            This might help you with the Pick-3, I have not experimented with the Pick-4 number, but perhaps this will point you in the right direction:

            Best to utilize a few more cells, assuming the number to be base sorted is in Cell A1, use Cells B1, C1, D1
            to sort and separate the individual numbers, then Concatenate the final results into another Cell (E1 in this instance)
            B1=MIN(LEFT(A1),MID(A1,2,1),RIGHT(A1))
            C1=MEDIAN(LEFT(A1),MID(A1,2,1),RIGHT(A1))
            D1=MAX(LEFT(A1),MID(A1,2,1),RIGHT(A1))
            E1=CONCATENATE(B1,C1,D1)
                      A1              B1            C1            D1              E1
            748
            4
            7
            8
            478
            start #
            Min
            Median
            Max
            end #
            There are probably as many ways to accomplish the end  result as there are programmers, and, I'm sure that some others will post their favorites.
            Good Luck to you ! Bob  Guitarbg
              SirMetro's avatar - center
              East of Atlanta
              United States
              Member #6191
              August 11, 2004
              1389 Posts
              Offline
              Posted: June 18, 2005, 12:54 pm - IP Logged

              That setup worked great. Only modification I did was to combine it into a single formula. I have no clue why I never thought of that. Guess that's what happens when I try to make things to damn complex.

              =CONCATENATE((MIN(LEFT($C2),MID($C2,2,1),RIGHT($C2))),(MEDIAN(LEFT($C2),MID($C2,2,1),RIGHT($C2))),(MAX(LEFT($C2),MID($C2,2,1),RIGHT($C2))))

              Change $C2 to reflect whatever cell you want it to reflect

                lottaloot's avatar - AvatarZ56
                Redford/MI
                United States
                Member #3396
                January 18, 2004
                4867 Posts
                Offline
                Posted: June 18, 2005, 1:20 pm - IP Logged
                Pick-3 & 4 in the Boxes Excel Formulas ---thanks go to  Adolf
                6166 
                =MIN(LEFT(A1),MID(A1,2,1),RIGHT(A1))
                =MEDIAN(LEFT(A1),MID(A1,2,1),RIGHT(A1)) 
                =MAX(LEFT(A1),MID(A1,2,1),RIGHT(A1)) 
                =MAX(LEFT(A1),MID(A1,2,1),RIGHT(A1),MID(A1,3,1)) 
                =CONCATENATE(B1,C1,D1,E1)
                ----------------------------------------
                123 
                =MIN(LEFT(A1),MID(A1,2,1),RIGHT(A1)) 
                =MEDIAN(LEFT(A1),MID(A1,2,1),RIGHT(A1)) 
                =MAX(LEFT(A1),MID(A1,2,1),RIGHT(A1)) 
                =CONCATENATE(B1,C1,D1)
                Good Luck!!
                Adolf
                  lottaloot's avatar - AvatarZ56
                  Redford/MI
                  United States
                  Member #3396
                  January 18, 2004
                  4867 Posts
                  Offline
                  Posted: June 18, 2005, 1:23 pm - IP Logged

                  That setup worked great. Only modification I did was to combine it into a single formula. I have no clue why I never thought of that. Guess that's what happens when I try to make things to damn complex.

                  =CONCATENATE((MIN(LEFT($C2),MID($C2,2,1),RIGHT($C2))),(MEDIAN(LEFT($C2),MID($C2,2,1),RIGHT($C2))),(MAX(LEFT($C2),MID($C2,2,1),RIGHT($C2))))

                  Change $C2 to reflect whatever cell you want it to reflect

                  SirMetro

                  Your formulas are always short & to the point

                    SirMetro's avatar - center
                    East of Atlanta
                    United States
                    Member #6191
                    August 11, 2004
                    1389 Posts
                    Offline
                    Posted: June 18, 2005, 2:03 pm - IP Logged

                    My thanks go to Adolf & BlueGrass.

                    The Cash3 works like a charm. That cash4 needs some help, it works on just 3 of the 4 digits. This one will probably require a macro instead of a formula because the 4th digit simply makes it complex.

                    The only other option I can think of that will retain it as a formula is to reflect ALL 24 ways the number can be reflected and do the MIN selection on it. Here is the revised formula for Cash 4

                    =MIN((CONCATENATE($C6,$D6,$E6,$F6)),(CONCATENATE($C6,$D6,$F6,$E6)),(CONCATENATE($C6,$E6,$D6,$F6)),(CONCATENATE($C6,$E6,$F6,$D6)),(CONCATENATE($C6,$F6,$D6,$E6)),(CONCATENATE($C6,$F6,$E6,$D6)),(CONCATENATE($D6,$C6,$E6,$F6)),(CONCATENATE($D6,$C6,$F6,$E6)),(CONCATENATE($D6,$E6,$C6,$F6)),(CONCATENATE($D6,$E6,$F6,$C6)),(CONCATENATE($D6,$F6,$C6,$E6)),(CONCATENATE($D6,$F6,$E6,$C6)),(CONCATENATE($E6,$C6,$D6,$F6)),(CONCATENATE($E6,$C6,$F6,$D6)),(CONCATENATE($E6,$D6,$C6,$F6)),(CONCATENATE($E6,$D6,$F6,$C6)),(CONCATENATE($E6,$F6,$C6,$D6)),(CONCATENATE($E6,$F6,$D6,$C6)),(CONCATENATE($F6,$C6,$D6,$E6)),(CONCATENATE($F6,$C6,$E6,$D6)),(CONCATENATE($F6,$D6,$C6,$E6)),(CONCATENATE($F6,$D6,$E6,$C6)),(CONCATENATE($F6,$E6,$C6,$D6)),(CONCATENATE($F6,$E6,$D6,$C6)))

                    BTW, the above could also be used for cash 3 if you adjust it for 3 numbers (6 way box). This formula currently reflects a 24 way box.

                    Sir Metro

                      winsumloosesum's avatar - Lottery-060.jpg
                      Pennsylvania
                      United States
                      Member #2218
                      September 1, 2003
                      5387 Posts
                      Online
                      Posted: June 20, 2005, 8:16 am - IP Logged

                      Thanks everyone for all your help.

                      Someone recommended the following:

                      1.  Download all my past drawings.

                      2.  Text to columns.

                      3.  Use the next four columns and use "=small(a1:d1,1) through =small(a1:d1,4)

                      4.  The next cell i1 "=e1&f1&g1&h1"

                      Thanks,

                      wsls

                        lottaloot's avatar - AvatarZ56
                        Redford/MI
                        United States
                        Member #3396
                        January 18, 2004
                        4867 Posts
                        Offline
                        Posted: June 20, 2005, 11:47 am - IP Logged

                        Thanks everyone for all your help.

                        Someone recommended the following:

                        1.  Download all my past drawings.

                        2.  Text to columns.

                        3.  Use the next four columns and use "=small(a1:d1,1) through =small(a1:d1,4)

                        4.  The next cell i1 "=e1&f1&g1&h1"

                        Thanks,

                        wsls

                        I like it!

                        I like it! Big Smile

                          MollyG's avatar - happy
                          TEXAS (yeah!)Katy
                          United States
                          Member #9019
                          November 25, 2004
                          523 Posts
                          Offline
                          Posted: June 20, 2005, 2:50 pm - IP Logged

                          Check my Blog.  I listed the formula IF the 3 numbers are in 3 columns.

                           

                          I guess there's more than one way to "skin a cat".....

                            lottaloot's avatar - AvatarZ56
                            Redford/MI
                            United States
                            Member #3396
                            January 18, 2004
                            4867 Posts
                            Offline
                            Posted: June 29, 2005, 12:24 am - IP Logged

                            It's me again. 

                            Looking for a formula that will return a result of how many days out a pair is.

                            Pair's location start in (V2) and in in V46.  Am I to use lookup? 

                            Any help is appreciated!!