generate all 7-letter formations, maximum 3 letters repeated.

piter · Oct 4, 2018

generate all 7-letter formations, maximum 3 letters repeated.
ex = aaabcde ... bbbcccd, etc.
we have 10 letters = abcdefghij to conbine the formations with 7 letters
please!

pgc01 · Oct 6, 2018

Not relevant now, but just out of curiosity,

another way to calculate the total number of combinations: all the combinations minus the combinations with 4 repeated elements:

=COMBINA(10,7)-10*COMBINA(10,3)

=9240

piter · Oct 6, 2018

ok PGC01, correct!!! thank you!

shg · Oct 6, 2018

COMBINA -- a multi-choose function. That was a nice addition (I use 2010).

=COMBIN(10 + 7 - 1, 7) - 10 * COMBIN(10 + 3 - 1, 3)

shg · Oct 6, 2018

piter said:
I just noticed that there are several patterns that have NEVER come out in the 20 year history of the UK Lotto. Not to say they won't ever appear, but these combos are seriouslyunlikely!

You realize those are not equally likely?

Here's is the frequency of those patterns for 49 choose 6, and the expected number of appearances in 7300 draws (daily for 20 years):

13,983,816
7,300
Pattern
Count
Expect
AAAAAA
84
0.04
AAAAAB
1,260
0.66
AAAABB
5,670
2.96
AAABBB
10,080
5.26
AABBBB
7,560
3.95
ABBBBB
2,268
1.18
BBBBBB
210
0.11
AAAAAC
1,260
0.66
AAAABC
12,600
6.58
AAABBC
37,800
19.73
AABBBC
43,200
22.55
ABBBBC
18,900
9.87
BBBBBC
2,520
1.32
AAAACC
5,670
2.96
AAABCC
37,800
19.73
AABBCC
72,900
38.06
ABBBCC
48,600
25.37
BBBBCC
9,450
4.93
AAACCC
10,080
5.26
AABCCC
43,200
22.55
ABBCCC
48,600
25.37
BBBCCC
14,400
7.52
AACCCC
7,560
3.95
ABCCCC
18,900
9.87
BBCCCC
9,450
4.93
ACCCCC
2,268
1.18
BCCCCC
2,520
1.32
CCCCCC
210
0.11
AAAAAD
1,260
0.66
AAAABD
12,600
6.58
AAABBD
37,800
19.73
AABBBD
43,200
22.55
ABBBBD
18,900
9.87
BBBBBD
2,520
1.32
AAAACD
12,600
6.58
AAABCD
84,000
43.85
AABBCD
162,000
84.57
ABBBCD
108,000
56.38
BBBBCD
21,000
10.96
AAACCD
37,800
19.73
AABCCD
162,000
84.57
ABBCCD
182,250
95.14
BBBCCD
54,000
28.19
AACCCD
43,200
22.55
ABCCCD
108,000
56.38
BBCCCD
54,000
28.19
ACCCCD
18,900
9.87
BCCCCD
21,000
10.96
CCCCCD
2,520
1.32
AAAADD
5,670
2.96
AAABDD
37,800
19.73
AABBDD
72,900
38.06
ABBBDD
48,600
25.37
BBBBDD
9,450
4.93
AAACDD
37,800
19.73
AABCDD
162,000
84.57
ABBCDD
182,250
95.14
BBBCDD
54,000
28.19
AACCDD
72,900
38.06
ABCCDD
182,250
95.14
BBCCDD
91,125
47.57
ACCCDD
48,600
25.37
BCCCDD
54,000
28.19
CCCCDD
9,450
4.93
AAADDD
10,080
5.26
AABDDD
43,200
22.55
ABBDDD
48,600
25.37
BBBDDD
14,400
7.52
AACDDD
43,200
22.55
ABCDDD
108,000
56.38
BBCDDD
54,000
28.19
ACCDDD
48,600
25.37
BCCDDD
54,000
28.19
CCCDDD
14,400
7.52
AADDDD
7,560
3.95
ABDDDD
18,900
9.87
BBDDDD
9,450
4.93
ACDDDD
18,900
9.87
BCDDDD
21,000
10.96
CCDDDD
9,450
4.93
ADDDDD
2,268
1.18
BDDDDD
2,520
1.32
CDDDDD
2,520
1.32
DDDDDD
210
0.11
AAAAAE
1,260
0.66
AAAABE
12,600
6.58
AAABBE
37,800
19.73
AABBBE
43,200
22.55
ABBBBE
18,900
9.87
BBBBBE
2,520
1.32
AAAACE
12,600
6.58
AAABCE
84,000
43.85
AABBCE
162,000
84.57
ABBBCE
108,000
56.38
BBBBCE
21,000
10.96
AAACCE
37,800
19.73
AABCCE
162,000
84.57
ABBCCE
182,250
95.14
BBBCCE
54,000
28.19
AACCCE
43,200
22.55
ABCCCE
108,000
56.38
BBCCCE
54,000
28.19
ACCCCE
18,900
9.87
BCCCCE
21,000
10.96
CCCCCE
2,520
1.32
AAAADE
12,600
6.58
AAABDE
84,000
43.85
AABBDE
162,000
84.57
ABBBDE
108,000
56.38
BBBBDE
21,000
10.96
AAACDE
84,000
43.85
AABCDE
360,000
187.93
ABBCDE
405,000
211.42
BBBCDE
120,000
62.64
AACCDE
162,000
84.57
ABCCDE
405,000
211.42
BBCCDE
202,500
105.71
ACCCDE
108,000
56.38
BCCCDE
120,000
62.64
CCCCDE
21,000
10.96
AAADDE
37,800
19.73
AABDDE
162,000
84.57
ABBDDE
182,250
95.14
BBBDDE
54,000
28.19
AACDDE
162,000
84.57
ABCDDE
405,000
211.42
BBCDDE
202,500
105.71
ACCDDE
182,250
95.14
BCCDDE
202,500
105.71
CCCDDE
54,000
28.19
AADDDE
43,200
22.55
ABDDDE
108,000
56.38
BBDDDE
54,000
28.19
ACDDDE
108,000
56.38
BCDDDE
120,000
62.64
CCDDDE
54,000
28.19
ADDDDE
18,900
9.87
BDDDDE
21,000
10.96
CDDDDE
21,000
10.96
DDDDDE
2,520
1.32
AAAAEE
5,670
2.96
AAABEE
37,800
19.73
AABBEE
72,900
38.06
ABBBEE
48,600
25.37
BBBBEE
9,450
4.93
AAACEE
37,800
19.73
AABCEE
162,000
84.57
ABBCEE
182,250
95.14
BBBCEE
54,000
28.19
AACCEE
72,900
38.06
ABCCEE
182,250
95.14
BBCCEE
91,125
47.57
ACCCEE
48,600
25.37
BCCCEE
54,000
28.19
CCCCEE
9,450
4.93
AAADEE
37,800
19.73
AABDEE
162,000
84.57
ABBDEE
182,250
95.14
BBBDEE
54,000
28.19
AACDEE
162,000
84.57
ABCDEE
405,000
211.42
BBCDEE
202,500
105.71
ACCDEE
182,250
95.14
BCCDEE
202,500
105.71
CCCDEE
54,000
28.19
AADDEE
72,900
38.06
ABDDEE
182,250
95.14
BBDDEE
91,125
47.57
ACDDEE
182,250
95.14
BCDDEE
202,500
105.71
CCDDEE
91,125
47.57
ADDDEE
48,600
25.37
BDDDEE
54,000
28.19
CDDDEE
54,000
28.19
DDDDEE
9,450
4.93
AAAEEE
10,080
5.26
AABEEE
43,200
22.55
ABBEEE
48,600
25.37
BBBEEE
14,400
7.52
AACEEE
43,200
22.55
ABCEEE
108,000
56.38
BBCEEE
54,000
28.19
ACCEEE
48,600
25.37
BCCEEE
54,000
28.19
CCCEEE
14,400
7.52
AADEEE
43,200
22.55
ABDEEE
108,000
56.38
BBDEEE
54,000
28.19
ACDEEE
108,000
56.38
BCDEEE
120,000
62.64
CCDEEE
54,000
28.19
ADDEEE
48,600
25.37
BDDEEE
54,000
28.19
CDDEEE
54,000
28.19
DDDEEE
14,400
7.52
AAEEEE
7,560
3.95
ABEEEE
18,900
9.87
BBEEEE
9,450
4.93
ACEEEE
18,900
9.87
BCEEEE
21,000
10.96
CCEEEE
9,450
4.93
ADEEEE
18,900
9.87
BDEEEE
21,000
10.96
CDEEEE
21,000
10.96
DDEEEE
9,450
4.93
AEEEEE
2,268
1.18
BEEEEE
2,520
1.32
CEEEEE
2,520
1.32
DEEEEE
2,520
1.32
EEEEEE
210
0.11

shg · Oct 6, 2018

And for the front- and back-end patterns, the expected number of occurrences:

	E	F	G	H	I
2	Front	Expect		Back	Expect
3	AAA	487.23		AAA	0.04
4	AAB	1202.48		AAB	0.66
5	AAC	423.97		AAC	0.66
6	AAD	87.11		AAD	0.66
7	AAE	3.95		AAE	0.66
8	ABB	1134.41		ABB	2.96
9	ABC	1059.93		ABC	6.58
10	ABD	217.77		ABD	6.58
11	ABE	9.87		ABE	6.58
12	ACC	369.06		ACC	2.96
13	ACD	217.77		ACD	6.58
14	ACE	9.87		ACE	6.58
15	ADD	61.79		ADD	2.96
16	ADE	9.87		ADE	6.58
17	AEE	1.18		AEE	2.96
18	BBB	306.08		BBB	10.50
19	BBC	529.97		BBC	53.47
20	BBD	108.88		BBD	53.47
21	BBE	4.93		BBE	53.47
22	BCC	410.07		BCC	88.09
23	BCD	241.96		BCD	195.76
24	BCE	10.96		BCE	195.76
25	BDD	68.66		BDD	88.09
26	BDE	10.96		BDE	195.76
27	BEE	1.32		BEE	88.09
28	CCC	94.98		CCC	82.06
29	CCD	108.88		CCD	356.90
30	CCE	4.93		CCE	356.90
31	CDD	68.66		CDD	466.89
32	CDE	10.96		CDE	1037.54
33	CEE	1.32		CEE	466.89
34	DDD	13.88		DDD	277.33
35	DDE	4.93		DDE	1145.82
36	DEE	1.32		DEE	1374.25
37	EEE	0.11		EEE	658.98
38
39
40		60.5%			64.2%
41		0.1%			0.2%

The yellow ones are those you haven't seen; not surprisingly, they are among least likely. And conversely, the most frequent appearing are among the most likely.

Your Lotto looks comfortingly random.

pgc01 · Oct 6, 2018

shg said:
COMBINA -- a multi-choose function. That was a nice addition (I use 2010).

I didn't know either. I checked which were the new functions for 2013 but I missed this one.
It was just when I started to write the formula and intellisence showed this new option that I realised that it existed.

IMO, these functions were lacking. If you had functions for combinations and permutations without repetition, it makes sense to have the same functions for the case of repetition.

Interesting how they built the names of the new functions differently for the 2 cases

COMBIN() - COMBINA()
PERMUT() - PERMUTATIONA()

??

shg · Oct 6, 2018

piter said:
For the front end triple, only 16 combinations make up 95% of the winning combinations. These are (listed by number of hits):
AAB, ABB, ABC, AAC, AAA, BBC, ACC, BCC, BBB, ACD, BCD, ABD, AAD, CCC, CCD and BBD

And, for the back-end triple only 15 combinations are used 95% of the time:
DEE, DDE, CDE, EEE, CDD, CEE, CCD, CCE, DDD, BCE, BDE, BCD, BDD, CCC and BBD.

As you should expect:

	I	J	K	L	M
2	Pattern	Expected Front		Pattern	Expected Back
3	AAB	1202.48		DEE	1374.25
4	ABB	1134.41		DDE	1145.82
5	ABC	1059.93		CDE	1037.54
6	BBC	529.97		EEE	658.98
7	AAA	487.23		CDD	466.89
8	AAC	423.97		CEE	466.89
9	BCC	410.07		CCD	356.90
10	ACC	369.06		CCE	356.90
11	BBB	306.08		DDD	277.33
12	BCD	241.96		BCD	195.76
13	ABD	217.77		BCE	195.76
14	ACD	217.77		BDE	195.76
15	BBD	108.88		BCC	88.09
16	CCD	108.88		BDD	88.09
17	CCC	94.98		BEE	88.09
18	AAD	87.11		CCC	82.06
19	BDD	68.66		BBC	53.47
20	CDD	68.66		BBD	53.47
21	ADD	61.79		BBE	53.47
22	DDD	13.88		BBB	10.50
23	BCE	10.96		ABC	6.58
24	BDE	10.96		ABD	6.58
25	CDE	10.96		ACD	6.58
26	ABE	9.87		ABE	6.58
27	ACE	9.87		ACE	6.58
28	ADE	9.87		ADE	6.58
29	BBE	4.93		ABB	2.96
30	CCE	4.93		ACC	2.96
31	DDE	4.93		ADD	2.96
32	AAE	3.95		AEE	2.96
33	BEE	1.32		AAB	0.66
34	CEE	1.32		AAC	0.66
35	DEE	1.32		AAD	0.66
36	AEE	1.18		AAE	0.66
37	EEE	0.11		AAA	0.04
38
39
40		95.9%			95.2%

Fluff · Oct 6, 2018

Cross posted https://www.excelforum.com/excel-pr...-being-a-maximum-of-3-letters-per-column.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

piter · Oct 6, 2018

hello SHG, great job, when winning a prize will be rewarded by 20%. thank you
fluff. Sorry, is that as you appreciate the solutions, we can move forward one more step,
to do the crossroads of the patterns, in the same post, thanks

shg · Oct 6, 2018

piter said:
SHG, great job, ...

Don't misinterpret that to mean that any draw is more likely than any other.

generate all 7-letter formations, maximum 3 letters repeated.

piter

Active Member

pgc01

MrExcel MVP

Excel Facts

piter

Active Member

shg

MrExcel MVP

shg

MrExcel MVP

shg

MrExcel MVP

pgc01

MrExcel MVP

shg

MrExcel MVP

Fluff

MrExcel MVP, Moderator

piter

Active Member

shg

MrExcel MVP

Similar threads

Forum statistics

Share this page

	13,983,816	7,300
Pattern	Count	Expect
AAAAAA	84	0.04
AAAAAB	1,260	0.66
AAAABB	5,670	2.96
AAABBB	10,080	5.26
AABBBB	7,560	3.95
ABBBBB	2,268	1.18
BBBBBB	210	0.11
AAAAAC	1,260	0.66
AAAABC	12,600	6.58
AAABBC	37,800	19.73
AABBBC	43,200	22.55
ABBBBC	18,900	9.87
BBBBBC	2,520	1.32
AAAACC	5,670	2.96
AAABCC	37,800	19.73
AABBCC	72,900	38.06
ABBBCC	48,600	25.37
BBBBCC	9,450	4.93
AAACCC	10,080	5.26
AABCCC	43,200	22.55
ABBCCC	48,600	25.37
BBBCCC	14,400	7.52
AACCCC	7,560	3.95
ABCCCC	18,900	9.87
BBCCCC	9,450	4.93
ACCCCC	2,268	1.18
BCCCCC	2,520	1.32
CCCCCC	210	0.11
AAAAAD	1,260	0.66
AAAABD	12,600	6.58
AAABBD	37,800	19.73
AABBBD	43,200	22.55
ABBBBD	18,900	9.87
BBBBBD	2,520	1.32
AAAACD	12,600	6.58
AAABCD	84,000	43.85
AABBCD	162,000	84.57
ABBBCD	108,000	56.38
BBBBCD	21,000	10.96
AAACCD	37,800	19.73
AABCCD	162,000	84.57
ABBCCD	182,250	95.14
BBBCCD	54,000	28.19
AACCCD	43,200	22.55
ABCCCD	108,000	56.38
BBCCCD	54,000	28.19
ACCCCD	18,900	9.87
BCCCCD	21,000	10.96
CCCCCD	2,520	1.32
AAAADD	5,670	2.96
AAABDD	37,800	19.73
AABBDD	72,900	38.06
ABBBDD	48,600	25.37
BBBBDD	9,450	4.93
AAACDD	37,800	19.73
AABCDD	162,000	84.57
ABBCDD	182,250	95.14
BBBCDD	54,000	28.19
AACCDD	72,900	38.06
ABCCDD	182,250	95.14
BBCCDD	91,125	47.57
ACCCDD	48,600	25.37
BCCCDD	54,000	28.19
CCCCDD	9,450	4.93
AAADDD	10,080	5.26
AABDDD	43,200	22.55
ABBDDD	48,600	25.37
BBBDDD	14,400	7.52
AACDDD	43,200	22.55
ABCDDD	108,000	56.38
BBCDDD	54,000	28.19
ACCDDD	48,600	25.37
BCCDDD	54,000	28.19
CCCDDD	14,400	7.52
AADDDD	7,560	3.95
ABDDDD	18,900	9.87
BBDDDD	9,450	4.93
ACDDDD	18,900	9.87
BCDDDD	21,000	10.96
CCDDDD	9,450	4.93
ADDDDD	2,268	1.18
BDDDDD	2,520	1.32
CDDDDD	2,520	1.32
DDDDDD	210	0.11
AAAAAE	1,260	0.66
AAAABE	12,600	6.58
AAABBE	37,800	19.73
AABBBE	43,200	22.55
ABBBBE	18,900	9.87
BBBBBE	2,520	1.32
AAAACE	12,600	6.58
AAABCE	84,000	43.85
AABBCE	162,000	84.57
ABBBCE	108,000	56.38
BBBBCE	21,000	10.96
AAACCE	37,800	19.73
AABCCE	162,000	84.57
ABBCCE	182,250	95.14
BBBCCE	54,000	28.19
AACCCE	43,200	22.55
ABCCCE	108,000	56.38
BBCCCE	54,000	28.19
ACCCCE	18,900	9.87
BCCCCE	21,000	10.96
CCCCCE	2,520	1.32
AAAADE	12,600	6.58
AAABDE	84,000	43.85
AABBDE	162,000	84.57
ABBBDE	108,000	56.38
BBBBDE	21,000	10.96
AAACDE	84,000	43.85
AABCDE	360,000	187.93
ABBCDE	405,000	211.42
BBBCDE	120,000	62.64
AACCDE	162,000	84.57
ABCCDE	405,000	211.42
BBCCDE	202,500	105.71
ACCCDE	108,000	56.38
BCCCDE	120,000	62.64
CCCCDE	21,000	10.96
AAADDE	37,800	19.73
AABDDE	162,000	84.57
ABBDDE	182,250	95.14
BBBDDE	54,000	28.19
AACDDE	162,000	84.57
ABCDDE	405,000	211.42
BBCDDE	202,500	105.71
ACCDDE	182,250	95.14
BCCDDE	202,500	105.71
CCCDDE	54,000	28.19
AADDDE	43,200	22.55
ABDDDE	108,000	56.38
BBDDDE	54,000	28.19
ACDDDE	108,000	56.38
BCDDDE	120,000	62.64
CCDDDE	54,000	28.19
ADDDDE	18,900	9.87
BDDDDE	21,000	10.96
CDDDDE	21,000	10.96
DDDDDE	2,520	1.32
AAAAEE	5,670	2.96
AAABEE	37,800	19.73
AABBEE	72,900	38.06
ABBBEE	48,600	25.37
BBBBEE	9,450	4.93
AAACEE	37,800	19.73
AABCEE	162,000	84.57
ABBCEE	182,250	95.14
BBBCEE	54,000	28.19
AACCEE	72,900	38.06
ABCCEE	182,250	95.14
BBCCEE	91,125	47.57
ACCCEE	48,600	25.37
BCCCEE	54,000	28.19
CCCCEE	9,450	4.93
AAADEE	37,800	19.73
AABDEE	162,000	84.57
ABBDEE	182,250	95.14
BBBDEE	54,000	28.19
AACDEE	162,000	84.57
ABCDEE	405,000	211.42
BBCDEE	202,500	105.71
ACCDEE	182,250	95.14
BCCDEE	202,500	105.71
CCCDEE	54,000	28.19
AADDEE	72,900	38.06
ABDDEE	182,250	95.14
BBDDEE	91,125	47.57
ACDDEE	182,250	95.14
BCDDEE	202,500	105.71
CCDDEE	91,125	47.57
ADDDEE	48,600	25.37
BDDDEE	54,000	28.19
CDDDEE	54,000	28.19
DDDDEE	9,450	4.93
AAAEEE	10,080	5.26
AABEEE	43,200	22.55
ABBEEE	48,600	25.37
BBBEEE	14,400	7.52
AACEEE	43,200	22.55
ABCEEE	108,000	56.38
BBCEEE	54,000	28.19
ACCEEE	48,600	25.37
BCCEEE	54,000	28.19
CCCEEE	14,400	7.52
AADEEE	43,200	22.55
ABDEEE	108,000	56.38
BBDEEE	54,000	28.19
ACDEEE	108,000	56.38
BCDEEE	120,000	62.64
CCDEEE	54,000	28.19
ADDEEE	48,600	25.37
BDDEEE	54,000	28.19
CDDEEE	54,000	28.19
DDDEEE	14,400	7.52
AAEEEE	7,560	3.95
ABEEEE	18,900	9.87
BBEEEE	9,450	4.93
ACEEEE	18,900	9.87
BCEEEE	21,000	10.96
CCEEEE	9,450	4.93
ADEEEE	18,900	9.87
BDEEEE	21,000	10.96
CDEEEE	21,000	10.96
DDEEEE	9,450	4.93
AEEEEE	2,268	1.18
BEEEEE	2,520	1.32
CEEEEE	2,520	1.32
DEEEEE	2,520	1.32
EEEEEE	210	0.11

generate all 7-letter formations, maximum 3 letters repeated.

Active Member

MrExcel MVP

Excel Facts

Active Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP, Moderator

Active Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock