Excel Formula for Pick 4

Reply #21

Tialuvslotto — Fri, 12 Aug 2016 11:08:49 GMT

Thanks, Winsum! Again! Appreciate the spreadsheet.

You are the Excelmeister

Reply #20

Tialuvslotto — Fri, 12 Aug 2016 11:04:12 GMT

Thanks for posting the link to the original thread, Raven. I forgot to do that

Reply #19

CARBOB — Fri, 12 Aug 2016 10:33:10 GMT

I don't see anything for a consecutive.

Reply #18

winsumloosesum — Fri, 12 Aug 2016 10:28:56 GMT

Or shorter version:

=IFERROR(VLOOKUP(MAX(FREQUENCY(C1:F1,C1:F1)),{1, NM ;3, T ;4, Q },2,0), LOOKUP(SUM(IF(FREQUENCY(C1:F1,C1:F1)=2,1)),{1, D ;2, DD }))

Control-Shift-Enter

Reply #17

Sunglasses — Thu, 11 Aug 2016 23:51:02 GMT

href

Reply #16

Raven62 — Thu, 11 Aug 2016 20:54:05 GMT

https://www.lotterypost.com/help/178423

Reply #15

Sunglasses — Thu, 11 Aug 2016 20:38:25 GMT

How to create a short circuit.

Reply #14

Raven62 — Thu, 11 Aug 2016 20:28:20 GMT

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

Reply #13

winsumloosesum — Thu, 11 Aug 2016 18:53:49 GMT

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

Reply #12

Sunglasses — Thu, 11 Aug 2016 13:20:23 GMT

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

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

That should work for pick 9.

Reply #11

CARBOB — Thu, 11 Aug 2016 11:40:08 GMT

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

Reply #10

LifesAGamble — Thu, 11 Aug 2016 11:32:10 GMT

I thank you too, ithastobesaid

Reply #9

Tialuvslotto — Thu, 11 Aug 2016 11:06:34 GMT

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

Reply #8

Sunglasses — Wed, 10 Aug 2016 22:22:08 GMT

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

Reply #7

Sunglasses — Wed, 10 Aug 2016 22:19:30 GMT

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

Reply #6

Sunglasses — Wed, 10 Aug 2016 21:47:30 GMT

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

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.

Reply #5

Sunglasses — Wed, 10 Aug 2016 21:38:28 GMT

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

Reply #4

ithastobesaid — Wed, 10 Aug 2016 21:30:03 GMT

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

n... [ More ]

Reply #3

Sunglasses — Wed, 10 Aug 2016 18:09:37 GMT

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

Reply #2

ithastobesaid — Wed, 10 Aug 2016 14:30:27 GMT

Oops - correction all the 000 should be 0000 .

Reply #1

ithastobesaid — Wed, 10 Aug 2016 14:29:41 GMT

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.

Excel Formula for Pick 4

Tialuvslotto — Wed, 10 Aug 2016 11:14:52 GMT

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 (Sing... [ More ]