Welcome Guest
You last visited December 5, 2016, 3:33 pm
All times shown are
Eastern Time (GMT-5:00)

# Excel Help Plese

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

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

Economy class
Belgium
Member #123700
February 27, 2012
4035 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
553 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
553 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
553 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!

United States
Member #124493
March 14, 2012
7023 Posts
Offline
 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
459 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
49699 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!

United States
Member #124493
March 14, 2012
7023 Posts
Offline
 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..

United States
Member #124493
March 14, 2012
7023 Posts
Offline
 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!

United States
Member #124493
March 14, 2012
7023 Posts
Offline
 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...

United States
Member #124493
March 14, 2012
7023 Posts
Offline
 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
886 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