+ Reply to Thread
Results 1 to 15 of 15

Categorize combinations of numbers-Lowest Median Highest

  1. #1
    Registered User
    Join Date
    03-01-2010
    Location
    new york, usa
    MS-Off Ver
    Excel 2003
    Posts
    37

    Categorize combinations of numbers-Lowest Median Highest

    I have a list of 3-digit numbers I want to use.
    I want to categorize each number as follows:

    L=Lowest M=Median H=Highest
    E.g.
    The number 123 has the following combinations: 123, 132, 213, 231, 312, 321
    and
    123 = LMH
    132 = LHM
    213 = MLH
    231 = MHL
    312 = HLM
    321 = HML

    A number like 799 would be
    I would like a function in Excel to take any such list of numbers (say in A) and find their LMH etc equivalents in a separate column B.
    Thanks in advance.
    Last edited by Sweetypie; 03-09-2010 at 10:25 AM.

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Categorize combinations of numbers-Lowest Median Highest

    You don't finish the example for 799. Would it be LHH, LMM, MHH, etc.? Also, will the numbers always be three digits?

  3. #3
    Registered User
    Join Date
    03-01-2010
    Location
    new york, usa
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Categorize combinations of numbers-Lowest Median Highest

    Quote Originally Posted by darkyam View Post
    You don't finish the example for 799. Would it be LHH, LMM, MHH, etc.? Also, will the numbers always be three digits?
    Thanks for replying darkyam.
    1. Yes a number like 799 or any number comprised of two separate digits only would be of the form LHH etc

    799=LHH
    979=HLH
    997=HHL

    2. The numbers will always be 3 digits form the set {0,1,2,3,4,5,6,7,8,9}

  4. #4
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Categorize combinations of numbers-Lowest Median Highest

    Easiest way I can think of is with helper columns, like in the attached. If you really need it all in one formula, that formula will be long (unless someone else comes up with a better idea), but it is possible.
    Attached Files Attached Files

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Categorize combinations of numbers-Lowest Median Highest

    Another (but similliar approach...)
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-01-2010
    Location
    new york, usa
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Categorize combinations of numbers-Lowest Median Highest

    Don't know if its my version of Excel (2007) but when I copy and paste the functions on the next row(s) they don't work--I get wrong digits or the dreaded #VALUE!

  7. #7
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Categorize combinations of numbers-Lowest Median Highest

    With whose approach? Can you paste a workbook showing what you did or the exact formulas as you have them along with a simple layout here?

  8. #8
    Registered User
    Join Date
    03-01-2010
    Location
    new york, usa
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Categorize combinations of numbers-Lowest Median Highest

    Appreciate the help so far. Actually i see where the problem is. I did specify that all the digits from 0 to 9 are included and can be in any position. So "012" and "O95" are a valid numbers.

  9. #9
    Registered User
    Join Date
    03-01-2010
    Location
    new york, usa
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Categorize combinations of numbers-Lowest Median Highest

    View of Sheet with ZBOR's approach..first 26 numbers. The last column to the right is the LMH etc column. It does work perfectly for numbers that do not begin with a zero (0).

    First image is problem section
    second image--shows where it works perfectly.
    Attached Images Attached Images
    Last edited by Sweetypie; 03-08-2010 at 05:50 PM. Reason: Additional Image--working part

  10. #10
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Categorize combinations of numbers-Lowest Median Highest

    You need to have your numbers formatted as text, rather than numbers with a leading 0 formatted in them. That applies to both of our approaches.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Categorize combinations of numbers-Lowest Median Highest

    I don't understand the logic you want applied.

    In your examples,

    100 = HLL
    101 = MLM
    122 = LMM

    Why not, for example,

    100 = HLL
    101 = HLH
    122 = LHH
    Entia non sunt multiplicanda sine necessitate

  12. #12
    Registered User
    Join Date
    03-01-2010
    Location
    new york, usa
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Categorize combinations of numbers-Lowest Median Highest

    Quote Originally Posted by shg View Post
    I don't understand the logic you want applied.

    In your examples,

    100 = HLL
    101 = MLM
    122 = LMM

    Why not, for example,

    100 = HLL
    101 = HLH
    122 = LHH
    That is OK too.

  13. #13
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Categorize combinations of numbers-Lowest Median Highest

    Here, try this formula in my example B1 and pull accross:

    =IF(4-COLUMN(A1)-LEN($A1)>0,0,--MID($A1,COLUMN(A1)-(3-LEN($A1)),1))

    (and format first column as 000)

    Edit. Note that in A column is number 2 (lenght=1) formated as 002 and not text 002 (lenght=3)
    Attached Files Attached Files
    Last edited by zbor; 03-09-2010 at 03:28 AM.

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Categorize combinations of numbers-Lowest Median Highest

    The below would (I think) replicate zbor's results using a single cell approach however I'm still not sure I entirely follow the required logic...

    B1:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUMPRODUCT(LOOKUP(-MID(TEXT($A1,"000"),{1;2;3},1),-LARGE(--MID(TEXT($A1,"000"),{1,2,3},1),{1;2;3}),{1,2,3}),{100;10;1}),3,"L"),2,"M"),1,"H")

  15. #15
    Registered User
    Join Date
    03-01-2010
    Location
    new york, usa
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Categorize combinations of numbers-Lowest Median Highest

    Darkyam, Zbor and DonkeyOte thanks so much. Donkeyote thanks especially for that formula for the LMH column..it worked with leading zeros.

    You "guys" are the best!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1