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.
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.Originally Posted by 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}
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.
Another (but similliar approach...)
"Relax. What is mind? No matter. What is matter? Never mind!"
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!
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?
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.
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.
Last edited by Sweetypie; 03-08-2010 at 05:50 PM. Reason: Additional Image--working part
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.
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
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
That is OK too.
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)
Last edited by zbor; 03-09-2010 at 03:28 AM.
"Relax. What is mind? No matter. What is matter? Never mind!"
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")
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks