Welcome Guest
Log In | Register )

Quick Links

### NetConnect

#### Internet Domains, simple and cheap

##### Find a domain name:

Home

The time is now 10:54 am
You last visited May 20, 2013, 10:54 am
All times shown are
Eastern Time (GMT-5:00)

# Excel Help Plese

Topic closed. 14 replies. Last post 12 months ago by lottoburg.

 Page 1 of 1
PrintE-mailLink
EAST COAST, USA
United States
Member #49886
January 31, 2007
540 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 #13375
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 ...

http://www.lotterypost.com/thread/114945

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 #5011
June 3, 2004
3502 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

5/6 and 8/10 winner
Belgium
Member #123712
February 27, 2012
1789 Posts
Online
 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.

*keep crunching*

.

EAST COAST, USA
United States
Member #49886
January 31, 2007
540 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 #49886
January 31, 2007
540 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 #49886
January 31, 2007
540 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!
NYC
United States
Member #124503
March 14, 2012
5008 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 #42276
June 23, 2006
236 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 #18150
June 28, 2005
17934 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!
NYC
United States
Member #124503
March 14, 2012
5008 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..

KEEP YOUR EYE ON THE BALL!
NYC
United States
Member #124503
March 14, 2012
5008 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!

KEEP YOUR EYE ON THE BALL!
NYC
United States
Member #124503
March 14, 2012
5008 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...

KEEP YOUR EYE ON THE BALL!
NYC
United States
Member #124503
March 14, 2012
5008 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 #54952
August 20, 2007
447 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