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

# Excel Users - Pick 3 & Pick 4

Topic closed. 23 replies. Last post 11 years ago by time*treat.

 Page 1 of 2
Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
 Posted: December 9, 2005, 11:04 am - IP Logged

For those who use Excel. Wanted to share this formula for those interested in converting a Pick 3 and Pick 4 drawn number to its ascending form.

Example: Pick 4    4 3 9 0 = 0 3 4 9

Pick 3    6 1 3 = 1 3 6

http://xcell05.free.fr/english/

Scroll down the page until you see MOREFUNC.XLL

Open Excel>Tools>AddIns>select Morefunc in the list and select OK

Go to the cell you want the Pick 3 or Pick 4 in ascending order and copy and paste the formula below into the formula box. You may need to change the formula from A1 in both places to reflect where you have the Pick 3 or Pick 4 number. The example below the formula is looking at cell A1 for either a Pick 3 or Pick 4 number. Press Control>Shift>Enter

--MCONCAT(VSORT(MID(A1,INTVECTOR(LEN(A1),1),1)+0,,1))

After you press Control>Shift>Enter you should now have the number in ascending order.

The Carolinas - Charlotte
United States
Member #21627
September 12, 2005
4138 Posts
Offline
 Posted: December 9, 2005, 11:07 am - IP Logged

Thanks winsumloosesum - that is very interesting.

Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
 Posted: December 9, 2005, 11:29 am - IP Logged

I should add this.  If you give away your excel file with this formula you might want to tell the person your giving this file to, to download this add-in also.  Or it won't work.

United States
Member #13130
March 30, 2005
2171 Posts
Offline
 Posted: December 9, 2005, 6:19 pm - IP Logged

nice little link. I guess you folks haven't gotten into writing your own functions yet? Then there is no need to provide extra instructions & steps when you give away a file. When you share your eXcel file, the functions & macros go along for the ride.

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

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: December 9, 2005, 6:48 pm - IP Logged

This formula will also convert the pick 3 into it's box form.  I am sure that it could be shortened but I don't know how to do it.

=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))))

L ttaL   T

United States
Member #13130
March 30, 2005
2171 Posts
Offline
 Posted: December 9, 2005, 7:28 pm - IP Logged

I'd hate to have to cut & paste THAT one a couple hundred times. Talk about parenthese purgatory. I have actually used longer versions of code when I thought it would make things easier to follow/edit later.

Maybe we could all create a clearinghouse for our formulas and macros.

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

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: December 9, 2005, 7:33 pm - IP Logged

It's not that difficult.  Just Left click of the mouse & pull down the formula.

I got no problem with it but if you could shorten it, I am sure it would be most appreciated.

L ttaL   T

United States
Member #13130
March 30, 2005
2171 Posts
Offline
 Posted: December 9, 2005, 8:07 pm - IP Logged

My formulas tend toward verbosity...

play with this and tell me how you like it... <-- maybe I should re-word this

create a macro/module page in XL & c & p this ...

'-------------------------------'

Function lowform_3(three_digit)
Dim channel() As Integer
Dim slot() As Integer
Dim num As Integer, pos As Integer
Dim c0, c1, c2, c3

c0 = three_digit
c1 = Int(c0 / 100)
c2 = Int((c0 Mod 100) / 10)
c3 = c0 Mod 10
ReDim channel(9)
ReDim slot(3)
channel(c1) = channel(c1) + 1
channel(c2) = channel(c2) + 1
channel(c3) = channel(c3) + 1
pos = 1
For num = 0 To 9
While channel(num) > 0
slot(pos) = num
channel(num) = channel(num) - 1
pos = pos + 1
Wend
Next num
lowform_3 = slot(1) * 100 + slot(2) * 10  + slot(3)
End Function

'-------------------------------'

now, anywhere on your worksheet, type =lowform_3(abc). press ENTER

include the "=" sign. "abc" is any number 000 to 999

you can also enter it direct in the formula bar.

I didn't zero out the arrays, cause I wrote this long ago, but to be more refined...

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

United States
Member #8160
October 26, 2004
6777 Posts
Offline
 Posted: December 9, 2005, 8:22 pm - IP Logged

Thank You!

United States
Member #13130
March 30, 2005
2171 Posts
Offline
 Posted: December 10, 2005, 10:06 am - IP Logged

You're welcome LC23.

It's nice to know my 1's and 0's are being acknowleged by someone.

I should also mention the above formula/function works with references, too (those R1C1, \$A\$1 thingies). I prefer functions to cell-by-cell formulas mainly for 2 reasons.

1. the file size is MUCH smaller, if you use 1000's of cells.

2. if you decide you want to change things, you do so in one place, one time. When the sheet is recalculated, everything is updated.

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

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: December 10, 2005, 11:30 am - IP Logged

I appreciate you sharing this with the forum.

I would love to know how to write macros.  Where did you learn to write them?   any books that you can refer?

I was thinking of taking a class on excel somethime in the future.

L ttaL   T

Pennsylvania
United States
Member #1340
April 6, 2003
2450 Posts
Offline
 Posted: December 10, 2005, 2:50 pm - IP Logged

here is a good place to see excel in action via tutorial examples...

http://www.exceltip.com/

covers macros and vba also

and here is a macro-specific one...

http://computerwhizzard.50megs.com/excelminitutorial.html

lottaloot: you don't need to exit excel to learn how to write and edit macros... It's all in the excel help files. :-)

Playing more than one ticket per game is betting against yourself.

United States
Member #17834
June 28, 2005
2083 Posts
Online
 Posted: December 14, 2005, 6:39 am - IP Logged

I wondering if any one has a excel formula that can do a skip and hit but, by a full combination (123)?

I want to write a formula or vb script that will go through all my states on my excel sheet and count how many skips inbetween for each hit of a combination I specify from state to state.

A push in the right direction would help also.

Any help would be great!!!

Good luck to everyone!!!

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: December 14, 2005, 3:41 pm - IP Logged

Q.  Does anyone know of a formula that will give the sum of a single cell for a pick 3 combo?  or do they always have to be in separate cells in order to return the sum??

Instead of 789 where C2=(7), C3=(8), C4=(9)

Have C1=(789) and then give the sum=(24) in C2

Is there any other way around the separate cells?

L ttaL   T

Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
 Posted: December 14, 2005, 4:03 pm - IP Logged

Kim,

http://www.exceltip.com/st/Sum_the_digits_numbers_in_a_cell_using_custom_VBA_function_in_Microsoft_Excel/620.html

 Page 1 of 2