Welcome Guest
Log In | Register )
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
PrintE-mailLink
winsumloosesum's avatar - Lottery-060.jpg
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

The first thing you will need is to download the FREE Add-in form the folowing link:

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

Scroll down the page until you see MOREFUNC.XLL

Download, unzip and install.

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.



    cps10's avatar - Lottery-004.jpg
    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.

      winsumloosesum's avatar - Lottery-060.jpg
      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.

        time*treat's avatar - radar

        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.

          lottaloot's avatar - AvatarZ56
          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

            time*treat's avatar - radar

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

            OogleI'd hate to have to cut & paste THAT one a couple hundred times. Evil LookingTalk 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.

              lottaloot's avatar - AvatarZ56
              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

                time*treat's avatar - radar

                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 Embarassed

                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.

                  LottoChica23's avatar - Aquarius
                  Fughedaboutit (NY)
                  United States
                  Member #8160
                  October 26, 2004
                  6777 Posts
                  Offline
                  Posted: December 9, 2005, 8:22 pm - IP Logged

                  Thank You! Smiley

                    time*treat's avatar - radar

                    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 See Ya!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.

                      lottaloot's avatar - AvatarZ56
                      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

                        hypersoniq's avatar - 8ball
                        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.

                          powerplayer's avatar - Lottery-022.jpg

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

                            lottaloot's avatar - AvatarZ56
                            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

                              winsumloosesum's avatar - Lottery-060.jpg
                              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