Welcome Guest
You last visited October 24, 2017, 7:24 am
All times shown are
Eastern Time (GMT-5:00)

# Excel Help--Low Middle High

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

 Page 1 of 1
New York, NY
United States
Member #39471
May 16, 2006
2699 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.

New York, NY
United States
Member #39471
May 16, 2006
2699 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.

\$\$\$

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..

New York, NY
United States
Member #39471
May 16, 2006
2699 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.

\$\$\$

 Page 1 of 1