Welcome Guest
Log In | Register )
You last visited January 20, 2017, 4:53 am
All times shown are
Eastern Time (GMT-5:00)

Excel Help--Low Middle High

Topic closed. 4 replies. Last post 7 years ago by Fibonacci.

Page 1 of 1
PrintE-mailLink
Fibonacci's avatar - Lottery-050.jpg
New York, NY
United States
Member #39471
May 16, 2006
2698 Posts
Offline
Posted: March 8, 2010, 1:04 pm - IP Logged

Need an Excel function/formula to do the following:

Take a 3 digit number in one column and report its LMH form, where L is the lowest digit, M=Median digit (in value) and H =highest digit.

It doesn't matter their position. Their value is what is important.

 

Eg

123  = LMH

132 = LHM

213 = MLH

231= LHM

312 = HLM

321 =HML

$$$


    United States
    Member #79057
    August 26, 2009
    70 Posts
    Offline
    Posted: March 8, 2010, 6:38 pm - IP Logged

    I'm sure there's probably and easier way, but this is what I'd do:

    First break the number down to three different columns

     

    ex:

    cell A1 is your number 369

    column b formula is               =trunc(a1*.01)                      gets 3

    Column c formula is              =trunc((a1-(b1*100))*.1)       gets 6

    Column d formula is             =a1-(b1*100)-(c1*10)            gets 9

     

    now that you've separated the number into 3 columns, you just need to check if it's LMH:

     

    column e is first number

    =if(b1=large(b1:d1,1),"H",if(b1=large(b1:d1,2),"M","L")

    repeat this formula in columns f & g only sub out b1 with c1 for column f and d1 for column g

    Should give you three columns L M H

     

    Hope this helps.  Hope is works as well... I occasionally drop a bracket or two when I'm working on my own stuff.  But this should do the trick.

      Fibonacci's avatar - Lottery-050.jpg
      New York, NY
      United States
      Member #39471
      May 16, 2006
      2698 Posts
      Offline
      Posted: March 8, 2010, 9:08 pm - IP Logged

      I'm sure there's probably and easier way, but this is what I'd do:

      First break the number down to three different columns

       

      ex:

      cell A1 is your number 369

      column b formula is               =trunc(a1*.01)                      gets 3

      Column c formula is              =trunc((a1-(b1*100))*.1)       gets 6

      Column d formula is             =a1-(b1*100)-(c1*10)            gets 9

       

      now that you've separated the number into 3 columns, you just need to check if it's LMH:

       

      column e is first number

      =if(b1=large(b1:d1,1),"H",if(b1=large(b1:d1,2),"M","L")

      repeat this formula in columns f & g only sub out b1 with c1 for column f and d1 for column g

      Should give you three columns L M H

       

      Hope this helps.  Hope is works as well... I occasionally drop a bracket or two when I'm working on my own stuff.  But this should do the trick.

      i HAVE SEEN A NUMBER OF WAYS TO BREAK UP A THREE DIGIT NUMBER INTO COLUMNS., including MID, yours is one ofn the most efficient and leading zeros are no problem. Thanks. I am testing it out.

      $$$

        tiggs95's avatar - Lottery-036.jpg

        United States
        Member #47420
        November 4, 2006
        3930 Posts
        Offline
        Posted: March 8, 2010, 10:52 pm - IP Logged

        I'm sure there's probably and easier way, but this is what I'd do:

        First break the number down to three different columns

         

        ex:

        cell A1 is your number 369

        column b formula is               =trunc(a1*.01)                      gets 3

        Column c formula is              =trunc((a1-(b1*100))*.1)       gets 6

        Column d formula is             =a1-(b1*100)-(c1*10)            gets 9

         

        now that you've separated the number into 3 columns, you just need to check if it's LMH:

         

        column e is first number

        =if(b1=large(b1:d1,1),"H",if(b1=large(b1:d1,2),"M","L")

        repeat this formula in columns f & g only sub out b1 with c1 for column f and d1 for column g

        Should give you three columns L M H

         

        Hope this helps.  Hope is works as well... I occasionally drop a bracket or two when I'm working on my own stuff.  But this should do the trick.

        I read some of these post's and I feel so stupid..I have no idea what this means and really don't care..Picking numbers should be easy and fun and to me this isen't..Good luck anyway..

          Fibonacci's avatar - Lottery-050.jpg
          New York, NY
          United States
          Member #39471
          May 16, 2006
          2698 Posts
          Offline
          Posted: March 9, 2010, 9:57 am - IP Logged

          Putz thanks again. I did run tinto some problem with the Trunc function ---when the last digit was a zero in some instances it put "10" in the cell--when it split a number--e.g. 120  to become  1  2  0 it did 1  2  10.

          Anyway I did get some additional help.

          $$$