Welcome Guest
Log In | Register )
You last visited December 7, 2016, 8:59 pm
All times shown are
Eastern Time (GMT-5:00)

Excel Formula for Pick 4

Topic closed. 21 replies. Last post 4 months ago by Tialuvslotto.

Page 1 of 2
51
PrintE-mailLink
Tialuvslotto's avatar - Jailin
Texas
United States
Member #150797
December 31, 2013
815 Posts
Offline
Posted: August 10, 2016, 7:14 am - IP Logged

Back in June, Winsum posted this excel solution to find singles, doubles and triples in P3:

"A1 = Pick 3 Number. Example 123

B1 = Value(mid(text($A1,"000"),1,1))

C1 = Value(mid(text($A1,"000"),2,1))

D1 = Value(mid(text($A1,"000"),3,1))

 

Cell E1 enter the following formula:

=CHOOSE(MAX(INDEX(COUNTIF($B1:$D1,$B1:$D1),)),"S","D","T"))"

Thanks, Winsum for your great work!  Much appreciated!

Does anyone know the corresponding formula for the P4?  The choices would be S (Single, 24-way), D (Double, 12 Way), DD (Double-Double, 6 way), T (Triple, 4 way) and Q (Quadruple).

"There is no such thing as luck; only adequate or inadequate preparation to cope with a statistical universe."

~Robert A. Heinlein

    Avatar

    United States
    Member #123200
    February 15, 2012
    52 Posts
    Offline
    Posted: August 10, 2016, 10:29 am - IP Logged

    A1 = 1234  (or 1224 or 1222 or 2222)

    B1 = VALUE(MID(TEXT($A1,"000"),1,1))

    C1 = VALUE(MID(TEXT($A1,"000"),2,1))

    D1 = VALUE(MID(TEXT($A1,"000"),3,1))

    E1 = VALUE(MID(TEXT($A1,"000"),4,1))

    F1 =CHOOSE(MAX(INDEX(COUNTIF($B1:$E1,$B1:$E1),)),"S","D","T","Q")

    Changes in bold.

      Avatar

      United States
      Member #123200
      February 15, 2012
      52 Posts
      Offline
      Posted: August 10, 2016, 10:30 am - IP Logged

      Oops - correction all the "000" should be "0000".

        Sunglasses's avatar - nicebear
        Zaperlopopotam
        Belgium
        Member #173932
        March 26, 2016
        958 Posts
        Offline
        Posted: August 10, 2016, 2:09 pm - IP Logged

        Back in June, Winsum posted this excel solution to find singles, doubles and triples in P3:

        "A1 = Pick 3 Number. Example 123

        B1 = Value(mid(text($A1,"000"),1,1))

        C1 = Value(mid(text($A1,"000"),2,1))

        D1 = Value(mid(text($A1,"000"),3,1))

         

        Cell E1 enter the following formula:

        =CHOOSE(MAX(INDEX(COUNTIF($B1:$D1,$B1:$D1),)),"S","D","T"))"

        Thanks, Winsum for your great work!  Much appreciated!

        Does anyone know the corresponding formula for the P4?  The choices would be S (Single, 24-way), D (Double, 12 Way), DD (Double-Double, 6 way), T (Triple, 4 way) and Q (Quadruple).

        Does anyone know the corresponding formula for the P4?  The choices would be S (Single, 24-way), D (Double, 12 Way), DD (Double-Double, 6 way), T (Triple, 4 way) and Q (Quadruple).

        Are you sober?

          Avatar

          United States
          Member #123200
          February 15, 2012
          52 Posts
          Offline
          Posted: August 10, 2016, 5:30 pm - IP Logged

          No need for snotty remarks.

          The simple functions used in the example provided are not enough to solve for the "DD" value. For this we need to know the number of unique values as well as the number of repeat values.  A simple VBA function will do this:

          Function Doit(num As Integer) As String

          Dim mynums(1 To 10) As Integer

          For i = 1 To 10

              mynums(i) = 0 

          Next i

          astr = Format(num, "0000")

          n1 = Int(Mid(astr, 1, 1)) 

          n2 = Int(Mid(astr, 2, 1)) 

          n3 = Int(Mid(astr, 3, 1)) 

          n4 = Int(Mid(astr, 4, 1)) 

          mynums(n1 + 1) = mynums(n1 + 1) + 1 

          mynums(n2 + 1) = mynums(n2 + 1) + 1 

          mynums(n3 + 1) = mynums(n3 + 1) + 1 

          mynums(n4 + 1) = mynums(n4 + 1) + 1 

          Set ws = Worksheets("Sheet1")

          uniq = 0 

          mymax = 0 

          For i = 1 To 10

              If mynums(i) > 0 Then

                  uniq = uniq + 1 

              End If

              If mynums(i) > mymax Then

                  mymax = mynums(i)

              End If

          Next i

          x = "n/a"

          Select Case uniq

              Case 1

                  x = "Q" 

              Case 2

                  If mymax = 3 Then

                      x = "T" 

                  Else

                      x = "DD"

                  End If

              Case 3

                  x = "D"

              Case 4

                  x = "S"

          End Select

          Doit = x

          End Function

          Use in your worksheet as follows: if cell A1 has 1234 in it, in cell B1 type "=Doit(A1)"   (without quotes). Change the "Doit" function name to whatever you want, just max sure to update the next to last line (Doit = x) to whatever you changed Doit to also.

            Sunglasses's avatar - nicebear
            Zaperlopopotam
            Belgium
            Member #173932
            March 26, 2016
            958 Posts
            Offline
            Posted: August 10, 2016, 5:38 pm - IP Logged
            N1N2N3N4N5C
            1556712211

            {=CONCAT(COUNTIF(B8:F8;B8:F8))}

            N1N2N3N4N5A
            1556712245

            {=CONCAT(RANK(B8:F8;B8:F8;1))}

              Sunglasses's avatar - nicebear
              Zaperlopopotam
              Belgium
              Member #173932
              March 26, 2016
              958 Posts
              Offline
              Posted: August 10, 2016, 5:47 pm - IP Logged
              N1N2N3N4N51234512345
              155671221131000
              555663332201100
              009912222112000

              With regular spreadsheet functions.

              A) Count each number, N1 to N5.

              B) Count each frequency and divide by the header. You can also do this in two steps.

              I'm sorry for you, that you learned so much nonsense in the forums.

              Notice that I do not use text.

                Sunglasses's avatar - nicebear
                Zaperlopopotam
                Belgium
                Member #173932
                March 26, 2016
                958 Posts
                Offline
                Posted: August 10, 2016, 6:19 pm - IP Logged
                12345R
                310001112
                0110023
                12000122

                {=CONCAT(REPT(K$1:O$1;K4:O4))}

                  Sunglasses's avatar - nicebear
                  Zaperlopopotam
                  Belgium
                  Member #173932
                  March 26, 2016
                  958 Posts
                  Offline
                  Posted: August 10, 2016, 6:22 pm - IP Logged
                  sdtqc--
                  12345ASCASC
                  310001112sssd
                  0110023dt
                  12000122sdd
                    Tialuvslotto's avatar - Jailin
                    Texas
                    United States
                    Member #150797
                    December 31, 2013
                    815 Posts
                    Offline
                    Posted: August 11, 2016, 7:06 am - IP Logged

                    No need for snotty remarks.

                    The simple functions used in the example provided are not enough to solve for the "DD" value. For this we need to know the number of unique values as well as the number of repeat values.  A simple VBA function will do this:

                    Function Doit(num As Integer) As String

                    Dim mynums(1 To 10) As Integer

                    For i = 1 To 10

                        mynums(i) = 0 

                    Next i

                    astr = Format(num, "0000")

                    n1 = Int(Mid(astr, 1, 1)) 

                    n2 = Int(Mid(astr, 2, 1)) 

                    n3 = Int(Mid(astr, 3, 1)) 

                    n4 = Int(Mid(astr, 4, 1)) 

                    mynums(n1 + 1) = mynums(n1 + 1) + 1 

                    mynums(n2 + 1) = mynums(n2 + 1) + 1 

                    mynums(n3 + 1) = mynums(n3 + 1) + 1 

                    mynums(n4 + 1) = mynums(n4 + 1) + 1 

                    Set ws = Worksheets("Sheet1")

                    uniq = 0 

                    mymax = 0 

                    For i = 1 To 10

                        If mynums(i) > 0 Then

                            uniq = uniq + 1 

                        End If

                        If mynums(i) > mymax Then

                            mymax = mynums(i)

                        End If

                    Next i

                    x = "n/a"

                    Select Case uniq

                        Case 1

                            x = "Q" 

                        Case 2

                            If mymax = 3 Then

                                x = "T" 

                            Else

                                x = "DD"

                            End If

                        Case 3

                            x = "D"

                        Case 4

                            x = "S"

                    End Select

                    Doit = x

                    End Function

                    Use in your worksheet as follows: if cell A1 has 1234 in it, in cell B1 type "=Doit(A1)"   (without quotes). Change the "Doit" function name to whatever you want, just max sure to update the next to last line (Doit = x) to whatever you changed Doit to also.

                    Thanks, ithastobesaid!  It was the "DD" value that was causing me trouble.

                    "There is no such thing as luck; only adequate or inadequate preparation to cope with a statistical universe."

                    ~Robert A. Heinlein

                      Avatar
                      Atlantic City, NJ
                      United States
                      Member #163215
                      January 23, 2015
                      47 Posts
                      Online
                      Posted: August 11, 2016, 7:32 am - IP Logged

                      I thank you too, ithastobesaid!

                        CARBOB's avatar - FL LOTTERY_LOGO.png
                        ORLANDO, FLORIDA
                        United States
                        Member #4924
                        June 3, 2004
                        5896 Posts
                        Offline
                        Posted: August 11, 2016, 7:40 am - IP Logged

                        Back in June, Winsum posted this excel solution to find singles, doubles and triples in P3:

                        "A1 = Pick 3 Number. Example 123

                        B1 = Value(mid(text($A1,"000"),1,1))

                        C1 = Value(mid(text($A1,"000"),2,1))

                        D1 = Value(mid(text($A1,"000"),3,1))

                         

                        Cell E1 enter the following formula:

                        =CHOOSE(MAX(INDEX(COUNTIF($B1:$D1,$B1:$D1),)),"S","D","T"))"

                        Thanks, Winsum for your great work!  Much appreciated!

                        Does anyone know the corresponding formula for the P4?  The choices would be S (Single, 24-way), D (Double, 12 Way), DD (Double-Double, 6 way), T (Triple, 4 way) and Q (Quadruple).

                        See if you like this better.

                        Ctrl-Shift-Enter

                        =IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},MID($E9101,{1,2,3,4},1)+0,0))),AND(ISNUMBER(MATCH(MIN(MID($E9101,{1,2,3,4},1)+0)+{0,1,2,3},MID($E9101,{1,2,3,4},1)+0,0)))),"C",IF(SUM(IF(FREQUENCY(MID($E9101,{1,2,3,4},1)+0,MID($E9101,{1,2,3,4},1)+0)=2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY(MID($E9101,{1,2,3,4},1)+0,MID($E9101,{1,2,3,4},1)+0)))))

                          Sunglasses's avatar - nicebear
                          Zaperlopopotam
                          Belgium
                          Member #173932
                          March 26, 2016
                          958 Posts
                          Offline
                          Posted: August 11, 2016, 9:20 am - IP Logged

                          =SUBSTITUTE(CONCAT(FREQUENCY(A1:D1;A1:D1));"0";"")

                          With order of first appearance. Frequency() takes matrix parameters.

                          That should work for pick 9.

                            winsumloosesum's avatar - Lottery-060.jpg
                            Pennsylvania
                            United States
                            Member #2218
                            September 1, 2003
                            5387 Posts
                            Offline
                            Posted: August 11, 2016, 2:53 pm - IP Logged

                            Back in June, Winsum posted this excel solution to find singles, doubles and triples in P3:

                            "A1 = Pick 3 Number. Example 123

                            B1 = Value(mid(text($A1,"000"),1,1))

                            C1 = Value(mid(text($A1,"000"),2,1))

                            D1 = Value(mid(text($A1,"000"),3,1))

                             

                            Cell E1 enter the following formula:

                            =CHOOSE(MAX(INDEX(COUNTIF($B1:$D1,$B1:$D1),)),"S","D","T"))"

                            Thanks, Winsum for your great work!  Much appreciated!

                            Does anyone know the corresponding formula for the P4?  The choices would be S (Single, 24-way), D (Double, 12 Way), DD (Double-Double, 6 way), T (Triple, 4 way) and Q (Quadruple).

                            I'm sure there's a shorter version than this:

                            =IF(SUMPRODUCT(($C1:$F1=$C1)+($C1:$F1=$D1)+($C1:$F1=$E1)+($C1:$F1=$F1))=16,"Q",IF(SUMPRODUCT(($C1:$F1=$C1)+($C1:$F1=$D1)+($C1:$F1=$E1)+($C1:$F1=$F1))=10,"T",IF(SUMPRODUCT(($C1:$F1=$C1)+($C1:$F1=$D1)+($C1:$F1=$E1)+($C1:$F1=$F1))=8,"DD",IF(SUMPRODUCT(($C1:$F1=$C1)+($C1:$F1=$D1)+($C1:$F1=$E1)+($C1:$F1=$F1))=6,"D","NM"))))

                            Example: Excel 2010

                            All 10,000 sets

                            https://1drv.ms/u/s!AifgvyqwTTMrg0dXbAgE5Tsge_xk

                              Raven62's avatar - binary
                              New Jersey
                              United States
                              Member #17843
                              June 28, 2005
                              49759 Posts
                              Online
                              Posted: August 11, 2016, 4:28 pm - IP Logged

                              Back in June, Winsum posted this excel solution to find singles, doubles and triples in P3:

                              "A1 = Pick 3 Number. Example 123

                              B1 = Value(mid(text($A1,"000"),1,1))

                              C1 = Value(mid(text($A1,"000"),2,1))

                              D1 = Value(mid(text($A1,"000"),3,1))

                               

                              Cell E1 enter the following formula:

                              =CHOOSE(MAX(INDEX(COUNTIF($B1:$D1,$B1:$D1),)),"S","D","T"))"

                              Thanks, Winsum for your great work!  Much appreciated!

                              Does anyone know the corresponding formula for the P4?  The choices would be S (Single, 24-way), D (Double, 12 Way), DD (Double-Double, 6 way), T (Triple, 4 way) and Q (Quadruple).

                              https://www.lotterypost.com/thread/303831/4638097

                              A mind once stretched by a new idea never returns to its original dimensions!