Welcome Guest

### NetConnect

#### Internet Domains, simple and cheap

##### Find a domain name:

Home

The time is now 1:31 pm
You last visited December 7, 2013, 1:30 pm
All times shown are
Eastern Time (GMT-5:00)

# Excel Help Plese

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

 Page 1 of 1
EAST COAST, USA
United States
Member #49428
January 31, 2007
546 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!

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

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

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
3709 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

Station C:\somewhere\in\space\time
Fiji
Member #123705
February 27, 2012
2193 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.

.

EAST COAST, USA
United States
Member #49428
January 31, 2007
546 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.

EAST COAST, USA
United States
Member #49428
January 31, 2007
546 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.

EAST COAST, USA
United States
Member #49428
January 31, 2007
546 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!

KEEP YOUR EYE ON THE BALL & REMEMBER TO SWING!
NYC
United States
Member #124498
March 14, 2012
6156 Posts
Online
 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?

United States
Member #41846
June 23, 2006
257 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.

New Jersey
United States
Member #17843
June 28, 2005
17936 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!

KEEP YOUR EYE ON THE BALL & REMEMBER TO SWING!
NYC
United States
Member #124498
March 14, 2012
6156 Posts
Online
 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..

KEEP YOUR EYE ON THE BALL & REMEMBER TO SWING!
NYC
United States
Member #124498
March 14, 2012
6156 Posts
Online
 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!

KEEP YOUR EYE ON THE BALL & REMEMBER TO SWING!
NYC
United States
Member #124498
March 14, 2012
6156 Posts
Online
 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!!!

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

KEEP YOUR EYE ON THE BALL & REMEMBER TO SWING!
NYC
United States
Member #124498
March 14, 2012
6156 Posts
Online
 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!!!

NYC
United States
Member #54483
August 20, 2007
573 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.

 Page 1 of 1