Welcome Guest
You last visited January 23, 2017, 9:53 pm
All times shown are
Eastern Time (GMT-5:00)

# Excel Help!!

Topic closed. 13 replies. Last post 12 years ago by lottaloot.

 Page 1 of 1
Pennsylvania
United States
Member #2218
September 1, 2003
5397 Posts
Online
 Posted: June 17, 2005, 6:31 pm - IP Logged

Excel professionals.

I am trying to find a formula for taking a Pick 3 and Pick 4 from its drawn combination and formulate it to the ascending order.

Example:

Pick 3  Cell A1  748

Formula to change it to: 478 in cell B1

Pick 4  Cell A1  7482

Formula to change it to: 2478 in cell B1

wsls

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: June 17, 2005, 10:29 pm - IP Logged

I am going to give this a whirl until SirMetro to the rescue.

Example:

Pick 3  Cell A1  748

Formula to change it to: 478 in cell B1

If the draw results were

B1=7

C1=4

D1=8

E1 =CONCATENATE(C1,B1,D1)  Results would be 478

Still trying to get the change to occur within the next cell by using the =SUM( LT)  =SUM (MID) functions

East of Atlanta
United States
Member #6191
August 11, 2004
1389 Posts
Offline
 Posted: June 17, 2005, 10:41 pm - IP Logged

Grrrr....I have been avoiding doing that one for a bit...guess I had better think bout it for a bit cause I could use it as well.

Sir Metro

Tx
United States
Member #4570
May 4, 2004
5180 Posts
Offline
 Posted: June 17, 2005, 11:07 pm - IP Logged

Can you all read minds?

I have been needing to do that to a list (single vertical column) of pick 3 numbers (combos) for a long long time and with no software to do it with (among other things that I have also been needing to do, but can't remember what they are just now, because I gave up on being able to do them time ago)

"Ten measures of beauty descended to the world, nine were taken by Jerusalem."

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: June 18, 2005, 3:04 am - IP Logged

Can anyone tell me what the function offset does?  In layman's terms.  I get easily

Killer's Cove
United States
Member #57
December 17, 2001
5663 Posts
Online
 Posted: June 18, 2005, 4:37 am - IP Logged

This might help you with the Pick-3, I have not experimented with the Pick-4 number, but perhaps this will point you in the right direction:

Best to utilize a few more cells, assuming the number to be base sorted is in Cell A1, use Cells B1, C1, D1
to sort and separate the individual numbers, then Concatenate the final results into another Cell (E1 in this instance)
B1=MIN(LEFT(A1),MID(A1,2,1),RIGHT(A1))
C1=MEDIAN(LEFT(A1),MID(A1,2,1),RIGHT(A1))
D1=MAX(LEFT(A1),MID(A1,2,1),RIGHT(A1))
E1=CONCATENATE(B1,C1,D1)
A1              B1            C1            D1              E1
 748 4 7 8 478 start # Min Median Max end #
There are probably as many ways to accomplish the end  result as there are programmers, and, I'm sure that some others will post their favorites.
Good Luck to you ! Bob  bg
East of Atlanta
United States
Member #6191
August 11, 2004
1389 Posts
Offline
 Posted: June 18, 2005, 12:54 pm - IP Logged

That setup worked great. Only modification I did was to combine it into a single formula. I have no clue why I never thought of that. Guess that's what happens when I try to make things to damn complex.

=CONCATENATE((MIN(LEFT(\$C2),MID(\$C2,2,1),RIGHT(\$C2))),(MEDIAN(LEFT(\$C2),MID(\$C2,2,1),RIGHT(\$C2))),(MAX(LEFT(\$C2),MID(\$C2,2,1),RIGHT(\$C2))))

Change \$C2 to reflect whatever cell you want it to reflect

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: June 18, 2005, 1:20 pm - IP Logged
Pick-3 & 4 in the Boxes Excel Formulas ---thanks go to  Adolf
6166
=MIN(LEFT(A1),MID(A1,2,1),RIGHT(A1))
=MEDIAN(LEFT(A1),MID(A1,2,1),RIGHT(A1))
=MAX(LEFT(A1),MID(A1,2,1),RIGHT(A1))
=MAX(LEFT(A1),MID(A1,2,1),RIGHT(A1),MID(A1,3,1))
=CONCATENATE(B1,C1,D1,E1)
----------------------------------------
123
=MIN(LEFT(A1),MID(A1,2,1),RIGHT(A1))
=MEDIAN(LEFT(A1),MID(A1,2,1),RIGHT(A1))
=MAX(LEFT(A1),MID(A1,2,1),RIGHT(A1))
=CONCATENATE(B1,C1,D1)
Good Luck!!
Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: June 18, 2005, 1:23 pm - IP Logged

That setup worked great. Only modification I did was to combine it into a single formula. I have no clue why I never thought of that. Guess that's what happens when I try to make things to damn complex.

=CONCATENATE((MIN(LEFT(\$C2),MID(\$C2,2,1),RIGHT(\$C2))),(MEDIAN(LEFT(\$C2),MID(\$C2,2,1),RIGHT(\$C2))),(MAX(LEFT(\$C2),MID(\$C2,2,1),RIGHT(\$C2))))

Change \$C2 to reflect whatever cell you want it to reflect

SirMetro

Your formulas are always short & to the point

East of Atlanta
United States
Member #6191
August 11, 2004
1389 Posts
Offline
 Posted: June 18, 2005, 2:03 pm - IP Logged

My thanks go to Adolf & BlueGrass.

The Cash3 works like a charm. That cash4 needs some help, it works on just 3 of the 4 digits. This one will probably require a macro instead of a formula because the 4th digit simply makes it complex.

The only other option I can think of that will retain it as a formula is to reflect ALL 24 ways the number can be reflected and do the MIN selection on it. Here is the revised formula for Cash 4

=MIN((CONCATENATE(\$C6,\$D6,\$E6,\$F6)),(CONCATENATE(\$C6,\$D6,\$F6,\$E6)),(CONCATENATE(\$C6,\$E6,\$D6,\$F6)),(CONCATENATE(\$C6,\$E6,\$F6,\$D6)),(CONCATENATE(\$C6,\$F6,\$D6,\$E6)),(CONCATENATE(\$C6,\$F6,\$E6,\$D6)),(CONCATENATE(\$D6,\$C6,\$E6,\$F6)),(CONCATENATE(\$D6,\$C6,\$F6,\$E6)),(CONCATENATE(\$D6,\$E6,\$C6,\$F6)),(CONCATENATE(\$D6,\$E6,\$F6,\$C6)),(CONCATENATE(\$D6,\$F6,\$C6,\$E6)),(CONCATENATE(\$D6,\$F6,\$E6,\$C6)),(CONCATENATE(\$E6,\$C6,\$D6,\$F6)),(CONCATENATE(\$E6,\$C6,\$F6,\$D6)),(CONCATENATE(\$E6,\$D6,\$C6,\$F6)),(CONCATENATE(\$E6,\$D6,\$F6,\$C6)),(CONCATENATE(\$E6,\$F6,\$C6,\$D6)),(CONCATENATE(\$E6,\$F6,\$D6,\$C6)),(CONCATENATE(\$F6,\$C6,\$D6,\$E6)),(CONCATENATE(\$F6,\$C6,\$E6,\$D6)),(CONCATENATE(\$F6,\$D6,\$C6,\$E6)),(CONCATENATE(\$F6,\$D6,\$E6,\$C6)),(CONCATENATE(\$F6,\$E6,\$C6,\$D6)),(CONCATENATE(\$F6,\$E6,\$D6,\$C6)))

BTW, the above could also be used for cash 3 if you adjust it for 3 numbers (6 way box). This formula currently reflects a 24 way box.

Sir Metro

Pennsylvania
United States
Member #2218
September 1, 2003
5397 Posts
Online
 Posted: June 20, 2005, 8:16 am - IP Logged

Thanks everyone for all your help.

Someone recommended the following:

2.  Text to columns.

3.  Use the next four columns and use "=small(a1:d1,1) through =small(a1:d1,4)

4.  The next cell i1 "=e1&f1&g1&h1"

Thanks,

wsls

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: June 20, 2005, 11:47 am - IP Logged

Thanks everyone for all your help.

Someone recommended the following:

2.  Text to columns.

3.  Use the next four columns and use "=small(a1:d1,1) through =small(a1:d1,4)

4.  The next cell i1 "=e1&f1&g1&h1"

Thanks,

wsls

I like it!

I like it!

TEXAS (yeah!)Katy
United States
Member #9019
November 25, 2004
523 Posts
Offline
 Posted: June 20, 2005, 2:50 pm - IP Logged

Check my Blog.  I listed the formula IF the 3 numbers are in 3 columns.

I guess there's more than one way to "skin a cat".....

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: June 29, 2005, 12:24 am - IP Logged

It's me again.

Looking for a formula that will return a result of how many days out a pair is.

Pair's location start in (V2) and in in V46.  Am I to use lookup?

Any help is appreciated!!

 Page 1 of 1