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

Excel Help Plese

Topic closed. 14 replies. Last post 5 years ago by lottoburg.

Page 1 of 1
51
PrintE-mailLink
Avatar
EAST COAST, USA
United States
Member #49428
January 31, 2007
553 Posts
Offline
Posted: May 16, 2012, 10:51 am - IP Logged

I am trying to create a formula with the following results:

 

Col. A     Col. B        Col. C                      Col. D   Box Order(Ascending)

    3           2              1                               123

    9           3               9                               399

    8            7              6                                678

 

I want to take numbers from 3 cells and combine them in ascending order. I need a formula in column D that will display the numbers as listed above.  Can anyone help? Please.  Thanks to all you Excel Gurus!

    time*treat's avatar - radar

    United States
    Member #13130
    March 30, 2005
    2171 Posts
    Offline
    Posted: May 16, 2012, 1:01 pm - IP Logged

    I thought you'd need a macro for this since the entry numbers won't be in a fixed order.

    But, it seems if you're willing to take an intermediate step ...

    http://www.lotterypost.com/thread/114945

    In neo-conned Amerika, bank robs you.
    Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

      CARBOB's avatar - FL LOTTERY_LOGO.png
      ORLANDO, FLORIDA
      United States
      Member #4924
      June 3, 2004
      5896 Posts
      Offline
      Posted: May 16, 2012, 1:13 pm - IP Logged

      Try this

      =MIN(a19:c19)&MEDIAN(a19:c19)&MAX(a19:c19)

      change cell range to yours

        SergeM's avatar - slow icon.png
        Economy class
        Belgium
        Member #123700
        February 27, 2012
        4035 Posts
        Offline
        Posted: May 16, 2012, 1:27 pm - IP Logged

        I am trying to create a formula with the following results:

         

        Col. A     Col. B        Col. C                      Col. D   Box Order(Ascending)

            3           2              1                               123

            9           3               9                               399

            8            7              6                                678

         

        I want to take numbers from 3 cells and combine them in ascending order. I need a formula in column D that will display the numbers as listed above.  Can anyone help? Please.  Thanks to all you Excel Gurus!

        1. You don't need VBA.
        2. Median won't work for Pick 4.

          Avatar
          EAST COAST, USA
          United States
          Member #49428
          January 31, 2007
          553 Posts
          Offline
          Posted: May 16, 2012, 3:59 pm - IP Logged

          Thanks a million time*treat.  I'm willing to try anything.  I've created a cumbersome macro and it takes a long time to process.  It handles only small amounts of data efficiently.

            Avatar
            EAST COAST, USA
            United States
            Member #49428
            January 31, 2007
            553 Posts
            Offline
            Posted: May 16, 2012, 4:00 pm - IP Logged

            Try this

            =MIN(a19:c19)&MEDIAN(a19:c19)&MAX(a19:c19)

            change cell range to yours

            thanks Cabob...I'll try this. Will let you know how it turns out.

              Avatar
              EAST COAST, USA
              United States
              Member #49428
              January 31, 2007
              553 Posts
              Offline
              Posted: May 16, 2012, 4:06 pm - IP Logged

              thanks Cabob...I'll try this. Will let you know how it turns out.

              Take your bow! You are an Excel Guru! This was very simple and easy to follow.  I am now going to have to reprogram a boat load of stuff but this certainly makes my life easier! You are the stuff!


                United States
                Member #124493
                March 14, 2012
                7023 Posts
                Offline
                Posted: May 16, 2012, 6:21 pm - IP Logged

                Take your bow! You are an Excel Guru! This was very simple and easy to follow.  I am now going to have to reprogram a boat load of stuff but this certainly makes my life easier! You are the stuff!

                good stuff carbob...

                now how do you take the new results and put each digit into new cells for filtering?

                  Avatar

                  United States
                  Member #41846
                  June 23, 2006
                  459 Posts
                  Offline
                  Posted: May 16, 2012, 8:39 pm - IP Logged

                  good stuff carbob...

                  now how do you take the new results and put each digit into new cells for filtering?

                  Carbobs method causes EXCEL to think that the result is text, not a number. for most purposes this is not a problem, just something to be aware of.  it does have the adavantage that the leading 0 is displayed. if it was a number the leading 0 would not be displayed.

                  Lotoboner to convert this back to a 3 digit number, lets assume the result is in A9, and you want your 3 digits in A10,11,and 12, it could be anywhere you just have to change the references.

                  in A10 enter =value(mid(a9,1,1)

                  in A11 enter =value(mid(a9,2,1)

                  in A12 enter =value(mid(a9,3,1)

                  your result is now a number again with each digit in its own cell.

                    Raven62's avatar - binary
                    New Jersey
                    United States
                    Member #17843
                    June 28, 2005
                    49699 Posts
                    Offline
                    Posted: May 17, 2012, 6:18 pm - IP Logged

                    I am trying to create a formula with the following results:

                     

                    Col. A     Col. B        Col. C                      Col. D   Box Order(Ascending)

                        3           2              1                               123

                        9           3               9                               399

                        8            7              6                                678

                     

                    I want to take numbers from 3 cells and combine them in ascending order. I need a formula in column D that will display the numbers as listed above.  Can anyone help? Please.  Thanks to all you Excel Gurus!

                    Cell D2: =SMALL($A2:$C2,1)*100+SMALL($A2:$C2,2)*10+SMALL($A2:$C2,3)*1

                    A mind once stretched by a new idea never returns to its original dimensions!


                      United States
                      Member #124493
                      March 14, 2012
                      7023 Posts
                      Offline
                      Posted: May 20, 2012, 5:35 pm - IP Logged

                      Carbobs method causes EXCEL to think that the result is text, not a number. for most purposes this is not a problem, just something to be aware of.  it does have the adavantage that the leading 0 is displayed. if it was a number the leading 0 would not be displayed.

                      Lotoboner to convert this back to a 3 digit number, lets assume the result is in A9, and you want your 3 digits in A10,11,and 12, it could be anywhere you just have to change the references.

                      in A10 enter =value(mid(a9,1,1)

                      in A11 enter =value(mid(a9,2,1)

                      in A12 enter =value(mid(a9,3,1)

                      your result is now a number again with each digit in its own cell.

                      thanks for the reply philight...i have not tried it yet but i made this post as a favorite so when it gets buried i can still find it...i dont normally do that type of analysis but i DO have a question on how to convert cells...using 10 20 30 as place holders...for example..


                        United States
                        Member #124493
                        March 14, 2012
                        7023 Posts
                        Offline
                        Posted: May 20, 2012, 5:38 pm - IP Logged

                        I am trying to create a formula with the following results:

                         

                        Col. A     Col. B        Col. C                      Col. D     Col. E      Col.  F

                            3           2              1                               13         22           31

                            9           3               9                               19         23           39

                            8            7              6                                18        27           36

                         

                        I want to take numbers from 3 cells and convert them. I need a formula to convert the three cells into differnt values as shown above.... Can anyone help? Please.  Thanks to all you Excel Gurus!


                          United States
                          Member #124493
                          March 14, 2012
                          7023 Posts
                          Offline
                          Posted: May 20, 2012, 5:41 pm - IP Logged

                          actually now that i look at it i can use a simple additive formulas...

                          10 + 3

                          20 + 2

                          30  + 1

                          DUH!!!  Hit With Stick

                          Well i guess i just want to be sure its that simple...


                            United States
                            Member #124493
                            March 14, 2012
                            7023 Posts
                            Offline
                            Posted: May 20, 2012, 5:45 pm - IP Logged

                            ok i just tried it...and it worked...so i guess i will delete my posts...or i will just leave them up so everybody can see what a bone head i can be!!!

                            simple...=bc1+10

                            wow!! I am an excel guru too!!!

                              lottoburg's avatar - wiggle
                              NYC
                              United States
                              Member #54483
                              August 20, 2007
                              886 Posts
                              Offline
                              Posted: May 31, 2012, 9:54 pm - IP Logged

                              Cell D2: =SMALL($A2:$C2,1)*100+SMALL($A2:$C2,2)*10+SMALL($A2:$C2,3)*1

                              Hi, Raven:

                              I sent a PM to you just now but your Inbox is full.