Welcome Guest
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
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

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.

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".

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?

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.

Zaperlopopotam
Belgium
Member #173932
March 26, 2016
958 Posts
Offline
 Posted: August 10, 2016, 5:38 pm - IP Logged
 N1 N2 N3 N4 N5 C 1 5 5 6 7 12211

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

 N1 N2 N3 N4 N5 A 1 5 5 6 7 12245

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

Zaperlopopotam
Belgium
Member #173932
March 26, 2016
958 Posts
Offline
 Posted: August 10, 2016, 5:47 pm - IP Logged
 N1 N2 N3 N4 N5 1 2 3 4 5 1 2 3 4 5 1 5 5 6 7 1 2 2 1 1 3 1 0 0 0 5 5 5 6 6 3 3 3 2 2 0 1 1 0 0 0 0 9 9 1 2 2 2 2 1 1 2 0 0 0

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.

Zaperlopopotam
Belgium
Member #173932
March 26, 2016
958 Posts
Offline
 Posted: August 10, 2016, 6:19 pm - IP Logged
 1 2 3 4 5 R 3 1 0 0 0 1112 0 1 1 0 0 23 1 2 0 0 0 122

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

Zaperlopopotam
Belgium
Member #173932
March 26, 2016
958 Posts
Offline
 Posted: August 10, 2016, 6:22 pm - IP Logged
 s d t q c - - 1 2 3 4 5 ASC ASC 3 1 0 0 0 1112 sssd 0 1 1 0 0 23 dt 1 2 0 0 0 122 sdd
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

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!

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

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.

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

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