How to reduce betting blokes that can guarantee max 1 incorrect

Kishan · Oct 2, 2019

Using Excel 2000
Hi,

Here I have a question how to reduce betting blokes that can guarantee max 1 incorrect, what I mean this, for example "Euromillions" playing 8 numbers direct we are playing 56 lines it cost 140€ "Formula=COMBIN(8,5)" if out of 8 numbers 5 numbers are correct we get the 1st price guarantee 100%. 
But instead 8 numbers direct if played in reduction we play only 5 lines it cost 12.50€ but it guarantee only 4 numbers can be correct if numbers drawn any 5 out of played 8. So far 1 number less. 

My question is about to play football reduction...it is difficult how do I explain it but here is my attempt...

Say for in example1 there are 2 teams the can have the end result in a 9 different ways as shown in the cells C6:D14...I can bet all 9 but as it is a money question I want to play less combinations but ensuring 1 must be correct out of any 9 results if I bet 3 lines as shown in the cells H6:I8 there is minimum 1 correct and 1 wrong. 

Example1...

Book1

A

B

C

D

E

F

G

H

I

J

K

1

2

3

4

5

Combi n

Team1

Team2

Combi n

Team1

Team2

6

1

7

2

1

X

2

X

8

3

1

2

3

2

1

9

4

X

1

10

5

X

11

6

X

2

12

7

2

1

13

8

2

X

14

9

2

15

16

17

18

Sheet2

Say for in example2 there are 3 teams the can have the end result in a 27 different ways as shown in the cells C6:E32...I can bet all 27 but as it is a money question I want to play less combinations but ensuring 1 must be correct out of any 27 results if I bet 6 lines as shown in the cells H6:J11 there is minimum 2 correct and one wrong.

Example2...

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

2

3

4

5

Combi n

Team1

Team2

Team3

Combi n

Team1

Team2

Team3

6

1

7

2

1

X

2

X

8

3

1

2

3

2

9

4

1

X

1

4

X

1

10

5

1

X

5

2

1

11

6

1

X

2

6

1

X

12

7

1

2

1

13

8

1

2

X

14

9

1

2

15

10

X

1

16

11

X

1

X

17

12

X

1

2

18

13

X

1

19

14

X

20

15

X

2

21

16

X

2

1

22

17

X

2

X

23

18

X

2

24

19

2

1

25

20

2

1

X

26

21

2

1

2

27

22

2

X

1

28

23

2

X

29

24

2

X

2

30

25

2

1

31

26

2

X

32

27

2

33

34

35

36

Sheet3

The above result I have come up checking one by one it has took 2, 3 days to ensure. 
And I guess they are right but not sure 100%

As the team increases lines increases...now I want to work out with 4 teams there would be 81 lines as per example3, is there any macro or VBA can extract only those lines out of 81 which has 3 correct and 1 wrong... manually I think it is not possible for me.

Example3...

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2

3

4

5

Combi n

Team1

Team2

Team3

Team4

Combi n

Team1

Team2

Team3

Team4

6

1

7

2

1

X

?

8

3

1

2

?

9

4

1

X

1

?

10

5

1

X

?

11

6

1

X

2

?

12

7

1

2

1

?

13

8

1

2

X

?

14

9

1

2

?

15

10

1

X

1

?

16

11

1

X

1

X

?

17

12

1

X

1

2

?

18

13

1

X

1

?

19

14

1

X

?

20

15

1

X

2

21

16

1

X

2

1

22

17

1

X

2

X

23

18

1

X

2

24

19

1

2

1

25

20

1

2

1

X

26

21

1

2

1

2

27

22

1

2

X

1

28

23

1

2

X

29

24

1

2

X

2

30

25

1

2

1

31

26

1

2

X

32

27

1

2

33

28

X

1

34

29

X

1

X

35

30

X

1

2

36

31

X

1

X

1

37

32

X

1

X

38

33

X

1

X

2

39

34

X

1

2

1

40

35

X

1

2

X

41

36

X

1

2

42

37

X

1

43

38

X

1

X

44

39

X

1

2

45

40

X

1

46

41

X

47

42

X

2

48

43

X

2

1

49

44

X

2

X

50

45

X

2

51

46

X

2

1

52

47

X

2

1

X

53

48

X

2

1

2

54

49

X

2

X

1

55

50

X

2

X

56

51

X

2

X

2

57

52

X

2

1

58

53

X

2

X

59

54

X

2

60

55

2

1

61

56

2

1

X

62

57

2

1

2

63

58

2

1

X

1

64

59

2

1

X

65

60

2

1

X

2

66

61

2

1

2

1

67

62

2

1

2

X

68

63

2

1

2

69

64

2

X

1

70

65

2

X

1

X

71

66

2

X

1

2

72

67

2

X

1

73

68

2

X

74

69

2

X

2

75

70

2

X

2

1

76

71

2

X

2

X

77

72

2

X

2

78

73

2

1

79

74

2

1

X

80

75

2

1

2

81

76

2

X

1

82

77

2

X

83

78

2

X

2

84

79

2

1

85

80

2

X

86

81

2

87

88

89

90

Sheet4

Thank you in advance

Regards,
Kishan

Eric W · Oct 4, 2019

I can't prove this is a minimal set, but I strongly believe it is:

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Values

1

2

X

2

Teams

4

3

4

5

Combination

Team 1

Team 2

Team 3

Team 4

Closest

Combination

Team 1

Team 2

Team 3

Team 4

6

1

7

2

1

2

1

14

1

2

8

3

1

X

1

27

1

X

9

4

1

2

1

33

2

1

2

X

10

5

1

2

14

43

2

X

1

11

6

1

2

X

33

47

2

X

1

2

12

7

1

X

1

62

X

1

X

2

13

8

1

X

2

62

66

X

2

1

X

14

9

1

X

27

76

X

2

1

15

10

1

2

1

16

11

1

2

1

2

14

17

12

1

2

1

X

66

18

13

1

2

1

14

19

14

1

2

14

20

15

1

2

X

14

21

16

1

2

X

1

43

22

17

1

2

X

2

14

23

18

1

2

X

27

24

19

1

X

1

25

20

1

X

1

2

47

26

21

1

X

1

X

27

22

1

X

2

1

76

28

23

1

X

2

14

29

24

1

X

2

X

27

30

25

1

X

1

27

31

26

1

X

2

27

32

27

1

X

27

33

28

2

1

34

29

2

1

2

47

35

30

2

1

X

33

36

31

2

1

2

1

33

37

32

2

1

2

33

38

33

2

1

2

X

33

39

34

2

1

X

1

43

40

35

2

1

X

2

62

41

36

2

1

X

33

42

37

2

1

43

38

2

1

2

47

44

39

2

1

X

66

45

40

2

1

43

46

41

2

14

47

42

2

X

33

48

43

2

X

1

43

49

44

2

X

2

43

50

45

2

X

43

51

46

2

X

1

47

52

47

2

X

1

2

47

53

48

2

X

1

X

47

54

49

2

X

2

1

76

55

50

2

X

2

47

56

51

2

X

2

X

33

57

52

2

X

1

43

58

53

2

X

2

47

59

54

2

X

27

60

55

X

1

61

56

X

1

2

62

57

X

1

X

66

63

58

X

1

2

1

76

64

59

X

1

2

62

65

60

X

1

2

X

33

66

61

X

1

X

1

62

67

62

X

1

X

2

62

68

63

X

1

X

62

69

64

X

2

1

66

70

65

X

2

1

2

66

71

66

X

2

1

X

66

72

67

X

2

1

76

73

68

X

2

14

74

69

X

2

X

66

75

70

X

2

X

1

43

76

71

X

2

X

2

62

77

72

X

2

X

66

78

73

X

1

76

79

74

X

1

2

47

80

75

X

1

X

66

81

76

X

2

1

76

82

77

X

2

76

83

78

X

2

X

76

84

79

X

1

76

85

80

X

2

62

86

81

X

27

Sheet1

I checked every combination up to 8 and did not find a match. I started running tests on combinations up to 11, but with some starting combinations included. I did not find a good set. I went back to a variation of the "Greedy" method. I went through all the combinations, and found the combination that matched the most other combinations. I flagged that one, and removed all the other combinations from consideration. I repeated the process on the remaining combinations until everything was accounted for. For 1 team and 2 teams, this algorithm came up with the same sets. For 3 teams, it came up with a set of 15! So it was with a lot of pessimism I tried on 4 teams, and with a lot of surprise when I got this list.

So I hope this helps you out! I won't be trying to lower the count, or working on 5 teams or more. (Although you can create a valid set for 5 teams by taking this list and adding 1,2,X to each combination for the Team 5 value. This gives you a set of 27.)

Good luck!

Kishan · Oct 4, 2019

Eric W said:
I can't prove this is a minimal set, but I strongly believe it is:

I checked every combination up to 8 and did not find a match. I started running tests on combinations up to 11, but with some starting combinations included. I did not find a good set. I went back to a variation of the "Greedy" method. I went through all the combinations, and found the combination that matched the most other combinations. I flagged that one, and removed all the other combinations from consideration. I repeated the process on the remaining combinations until everything was accounted for. For 1 team and 2 teams, this algorithm came up with the same sets. For 3 teams, it came up with a set of 15! So it was with a lot of pessimism I tried on 4 teams, and with a lot of surprise when I got this list.

So I hope this helps you out! I won't be trying to lower the count, or working on 5 teams or more. (Although you can create a valid set for 5 teams by taking this list and adding 1,2,X to each combination for the Team 5 value. This gives you a set of 27.)

Good luck!

Hi Eric, I check all 9 you have find out of 81sets, 9 covers 4 correct and rest 72 has got 3 covered that is what I wanted thank you very much for your kind help 

That all you have checked one by one manually till you did find these 9 unique with 3 and 4 matches against 81 sets but is there any VBA or formula way to which could make this search easier. Because with 5 team would be 243 direct 3*3*3*3*3=243 to find as you say may could be 27 will take time to finishes may be till end of this year. 

Good Luck to you as well 

Kind Regards,
Kishan

Kishan · Oct 5, 2019

Hi,

Thinking a lot working heard but do not find the way in which Eric has find in the post#11 precise only 9 sets which cover "minimum 3 within each 4 teams with possible 81 sets" may some one can make a macro to find them automatically... 

Kind Regards,
Kishan

Eric W · Oct 5, 2019

1) I used a variety of macros to create that set. However, there's no point in posting them. The "exhaustive" one would take far too long. I already tried the "greedy" one and it returned a set of around 36 combinations, more than the 27-member set I proposed. It was just kind of a fluke that it worked on the 4-Team problem.

2) I mentioned how to create the result for the 5-Team problem starting with the 4-Team solution. Here's a more detailed explanation. Start by writing each of the 4-Team combinations 3 times. Add a column for Team 5. For each of these new lines, put 1, 2, and X in the Team 5 position, like this:

Excel 2012

	A	B	C	D	E	G	H	I	J	K	L
1
2	Combination	Team 1	Team 2	Team 3	Team 4	Combination	Team 1	Team 2	Team 3	Team 4	Team 5
3	1	1	1	1	1	1	1	1	1	1	1
4						2	1	1	1	1	2
5						3	1	1	1	1	X
6
7	14	1	2	2	2	?	1	2	2	2	1
8						?	1	2	2	2	2
9						?	1	2	2	2	X
10
11	27	1	X	X	X	?	1	X	X	X	1
12						?	1	X	X	X	2
13						?	1	X	X	X	X
14
15	33	2	1	2	X	?	2	1	2	X	1
16						?	2	1	2	X	2
17						?	2	1	2	X	X

Sheet9

This example shows how to do the first 4 combinations, finish with the other 5. Now think about why this set works. We know that for any possible 5-Team combination, for the first 4 teams every possible combination is covered by 3 or 4 matches. Now if you create 3 versions of each combination, with 1, 2, and X, we know that one of those will match whatever Team 5 has. This means that out of the 3-combination group, one of them will match Team 5's result, therefore, out of the 3-combination set, one of them will have a 4 or 5 team match.

I'm pretty sure that this is not a minimal set, but it's still better than the 36 combination set my macro came up with. You can easily extend this process to 6- 7- 8- teams or more, but none of them will be optimal sets.

3) I'm pretty sure there must be a way to construct optimal sets reliably, but I just don't know the method. By looking at the 4 answers we've found so far, we can see some patterns. Every set contains a combination that is full row of 1s. Every set contains a combination that starts with 1, and the rest is 2s. Almost every set has a combination that is a full row of Xs. By looking at these patterns, someone might be able to figure out a construction method. I don't have the time to devote to it, or to research if someone's already done it. And there's even a larger question. Even if you could come up with such a set, would it help you with sports betting? I suspect not, since the people making the odds have devoted much more time to calculating the odds than I have here. There are some famous examples where the oddsmakers goofed, and someone took advantage of the mathematical errors, so it is possible, but it would take a lot more analysis than what you have here.

Good luck.

Kishan · Oct 5, 2019

Eric W said:
1) I used a variety of macros to create that set. However, there's no point in posting them. The "exhaustive" one would take far too long. I already tried the "greedy" one and it returned a set of around 36 combinations, more than the 27-member set I proposed. It was just kind of a fluke that it worked on the 4-Team problem.

2) I mentioned how to create the result for the 5-Team problem starting with the 4-Team solution. Here's a more detailed explanation. Start by writing each of the 4-Team combinations 3 times. Add a column for Team 5. For each of these new lines, put 1, 2, and X in the Team 5 position, like this:

Excel 2012

A
B
C
D
E
F
G
H
I
J
K
L

1

2
Combination
Team 1
Team 2
Team 3
Team 4
Combination
Team 1
Team 2
Team 3
Team 4
Team 5

3
1
1
1
1
1
1
1
1
1
1
1

4
2
1
1
1
1
2

5
3
1
1
1
1
X

6

7
14
1
2
2
2
?
1
2
2
2
1

8
?
1
2
2
2
2

9
?
1
2
2
2
X

10

11
27
1
X
X
X
?
1
X
X
X
1

12
?
1
X
X
X
2

13
?
1
X
X
X
X

14

15
33
2
1
2
X
?
2
1
2
X
1

16
?
2
1
2
X
2

17
?
2
1
2
X
X

<TBODY>
</TBODY>
Sheet9

This example shows how to do the first 4 combinations, finish with the other 5. Now think about why this set works. We know that for any possible 5-Team combination, for the first 4 teams every possible combination is covered by 3 or 4 matches. Now if you create 3 versions of each combination, with 1, 2, and X, we know that one of those will match whatever Team 5 has. This means that out of the 3-combination group, one of them will match Team 5's result, therefore, out of the 3-combination set, one of them will have a 4 or 5 team match.

I'm pretty sure that this is not a minimal set, but it's still better than the 36 combination set my macro came up with. You can easily extend this process to 6- 7- 8- teams or more, but none of them will be optimal sets.

3) I'm pretty sure there must be a way to construct optimal sets reliably, but I just don't know the method. By looking at the 4 answers we've found so far, we can see some patterns. Every set contains a combination that is full row of 1s. Every set contains a combination that starts with 1, and the rest is 2s. Almost every set has a combination that is a full row of Xs. By looking at these patterns, someone might be able to figure out a construction method. I don't have the time to devote to it, or to research if someone's already done it. And there's even a larger question. Even if you could come up with such a set, would it help you with sports betting? I suspect not, since the people making the odds have devoted much more time to calculating the odds than I have here. There are some famous examples where the oddsmakers goofed, and someone took advantage of the mathematical errors, so it is possible, but it would take a lot more analysis than what you have here.

Good luck.

Hi Eric, thank you for your time and support it has been greater help of you now it is more clearer to understand for me you have made is quite easer. 

Have a nice weekend and Good Luck.

Kind Regards,
Kishan

How to reduce betting blokes that can guarantee max 1 incorrect

Kishan

Well-known Member

Eric W

MrExcel MVP

Excel Facts

Kishan

Well-known Member

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

How to reduce betting blokes that can guarantee max 1 incorrect

Kishan

Well-known Member

Eric W

MrExcel MVP

Excel Facts

Kishan

Well-known Member

Kishan

Well-known Member

Eric W

MrExcel MVP

Kishan

Well-known Member

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