Math Problem in Excel

[smandula]

This is dealing with lottery odds.

For instance random numbers, any order, in picking 6 numbers out of 49
numbers

6/6 is 1 in 13,983,816 odds
=COMBIN(49,6)

Numbers in order or in sequence:
However, in sequence, of 4 numbers in a row, such as 1 2 3 4 x x out
of 49 numbers.

what are the odds for this calculation, and how would you calculate
this in Excel.

Further, if this sequence is at the beginning of 49 numbers would the
odds be any
different than 4 number in sequence at the end, such as x x 46 47 48
49

With Thanks

 

[joeu2004]

For instance random numbers, any order, in picking
6 numbers out of 49 numbers [...] is 1 in 13,983,816 odds
=COMBIN(49,6) [....]
However, in sequence, of 4 numbers in a row, such as
1 2 3 4 x x out of 49 numbers.

The number of such sequences is 46*COMBIN(45,2)

Explanation.... There are 49-4+1 combinations of 4 contiguous
numbers, i.e. 49 48 47 46, 48 47 46 45, ..., 4 3 2 1. That leaves 45
numbers from which any 2 are chosen at random.

Further, if this sequence is at the beginning of 49
numbers would the odds be any different than 4 number
in sequence at the end, such as x x 46 47 48 49

No. I hope that is apparent from my explanation.

 

[smandula]

The number of such sequences is 46*COMBIN(45,2)

To clarify: any of 4 contiguous numbers will have the same odds,
regardless of where being located sequentially at the beginning,
middle, or end of
a number series 1 to 49 as in Lotto 6/49.

Result: =46*COMBIN(45,2) ---> 1 to 45540 odds

It's hard to believe. I would think, that beginning contiguous numbers
would be lower odds, as opposed
higher numbers which would have higher odds.

I have nothing to base this thought on.

With Thanks

 

[joeu2004]

To clarify: any of 4 contiguous numbers will have the
same odds, regardless of where being located sequentially
at the beginning, middle, or end of a number series 1 to 49
[....] Result: =46*COMBIN(45,2) ---> 1 to 45540 odds

Right. But that is the number of combinations with a consecutive
sequence of __at_least__ 4.

If you want the number of combinations with a consecutive sequence of
__exactly__ 4, the formula is:

2*COMBIN(44,2)+44*COMBIN(43,2) = 41624

Explanation.... For the sequences 4-3-2-1 and 49-48-47-46, only 44 of
the remaining 45 can be used for the remaining pair; we must exclude 5
for the sequence 4-3-2-1, and we must exclude 45 for the sequence
49-48-47-46. For the 44 remaining sequences, only 43 of the remaining
45 can be used for the remaining pair; we must exclude the numbers
before and after the sequence.

I would think, that beginning contiguous numbers would
be lower odds, as opposed higher numbers which would
have higher odds.

Wrong. But what can I say to convince you? Do you think the numbers
care whether they are high or low? Is there a greater chance of
selecting 49 than selecting 1 because 49 is higher? (No and no.)

As I noted above, when counting combinations of sequences of
__exactly__ 4, there is a difference between "end sequences" and
"middle sequences" because there is only one way to extend "end
sequences" v. two ways to extend "middle sequences".

But there is no difference between the "low end sequence" (4-3-2-1)
and the "high end sequence" (49-48-47-46). And there is no difference
among the "middle sequences".

But you did not ask: what are the odds of selecting an "end sequence"
v. a "middle sequenece"? You asked: what are the odds of selecting
any sequence?

Perhaps an excercise with 4 dice will help you understand.

There are 6-2+1 = 5 sequences of 2, namely: 6-5, 5-4, 4-3, 3-2 and
2-1.

The number of combinations with a sequence of __at_least__ 2 is
5*COMBIN(4,2) = 30.

The number of combinations with a sequence of __exactly__ 2 is
2*COMBIN(3,2)+3*COMBIN(2,2) = 9. We can enumerate the 9:

end sequences: middle sequences:
6-5-3-2 5-4-2-1
6-5-3-1
6-5-2-1 4-3-6-1

2-1-6-5 3-2-6-5
2-1-6-4
2-1-5-4

-----

The VBA macros below count all the possible combinations with a
consecutive sequence of at least and exactly 4.

The output from atleast4() is:

4: 990
45540

Interpretation.... For the sequence starting with 4 (4,3,2,1), there
are 990 combinations. The same for all other sequences, since no
others are shown. The total number is 45540.

The output from exactly4() is:

4: 946
5: 903
49: 946
41624

Interpretation.... For the sequence starting with 4 (4,3,2,1) and 49
(49,48,47,46), there are 946 combinations each. For the sequence
starting with 5 (5,4,3,2), there are 903 combinations. For all other
sequences, the number of combinations is the same as for 5,4,3,2
(903), since no others are shown. The total number is 41624.

------

Option Explicit

Sub atleast4()
Dim i As Long, j As Long, k As Long, n As Long
Dim m As Long, m0 As Long, s As String
'n counts total combinations
n = 0: m0 = 0
For i = 4 To 49
'the consecutive sequence is i-3 to i
'm counts combinations for each sequence
m = 0
For j = 1 To 48
If j < i - 3 Or j > i Then
For k = j + 1 To 49
If k < i - 3 Or k > i Then n = n + 1: m = m + 1
Next
End If
Next
'm0 is the previous m
If m <> m0 Then s = s & i & ": " & m & Chr(10)
m0 = m
Next
MsgBox s & n
End Sub

Sub exactly4()
Dim i As Long, j As Long, k As Long, n As Long
Dim m As Long, m0 As Long, s As String
'n counts total combinations
n = 0: m0 = 0
For i = 4 To 49
'the consecutive sequence is i-3 to i
'm counts combinations for each sequence
m = 0
For j = 1 To 48
If j < i - 3 Or j > i Then
For k = j + 1 To 49
If k < i - 3 Or k > i Then
'exclude consecutive sequences > 4
If Not (j = i - 4 Or k = i - 4 Or _
j = i + 1 Or k = i + 1) _
Then n = n + 1: m = m + 1
End If
Next
End If
Next
'm0 is the previous m
If m <> m0 Then s = s & i & ": " & m & Chr(10)
m0 = m
Next
MsgBox s & n
End Sub

 

[joeu2004]

I would think, that beginning contiguous numbers would
be lower odds, as opposed higher numbers which would
have higher odds.

[....]
Perhaps an excercise with 4 dice will help you understand.
There are 6-2+1 = 5 sequences of 2, namely: 6-5, 5-4, 4-3,
3-2 and 2-1.
The number of combinations with a sequence of __at_least__
2 is 5*COMBIN(4,2) = 30.

To demonstrate that the number of combinations for each sequence is
the same for high-, middle- and low-numbered sequences, here is an
enumeration of the 30 combinations.

6-5-4-3 4-3-6-5 3-2-6-5
6-5-4-2 4-3-6-2 3-2-6-4
6-5-4-1 4-3-6-1 3-2-6-1
6-5-3-2 4-3-5-2 3-2-5-4
6-5-3-1 4-3-5-1 3-2-5-1
6-5-2-1 4-3-2-1 3-2-4-1

5-4-6-3 2-1-6-5
5-4-6-2 2-1-6-4
5-4-6-1 2-1-6-3
5-4-3-2 2-1-5-4
5-4-3-1 2-1-5-3
5-4-2-1 2-1-4-3

 

[joeu2004]

Errata....

But you did not ask:  what are the odds of selecting an
"end sequence" v. a "middle sequenece"?  You asked:
 what are the odds of selecting any sequence?

Sorry, I might have misinterpreted your original question.

However, in sequence, of 4 numbers in a row, such as
1 2 3 4 x x out of 49 numbers.

The number of combinations with __any_particular__ consecutive
sequence of __at_least__ 4 is COMBIN(45,2) = 990, as demonstrated by
the atleast4() macro that I posted previously.

Thus, the probability of 1-2-3-4-x-x is 990/13,983,816.

The number of combinations with __a_particular__ consecutive sequence
of __exactly__ 4 is COMBIN(44,2) = 946 for 4-3-2-1-x-x and 49-48-47-46-
x-x, and it is COMBIN(43,2) = 903 for all other sequences. That is
demonstrated by the exactly4() macro.

if this sequence is at the beginning of 49 numbers would
the odds be any different than 4 number in sequence at the
end, such as x x 46 47 48 49

No, not for 49-48-47-46.

But for __a_particular__ sequence of __exactly__ 4, yes, the odds are
different for the "middle sequences" -- any sequence other than
4-3-2-1-x-x and 49-48-47-46-x-x.

But the odds are the __same__ for __all__ "middle sequences".

smandula wrote later:

It's hard to believe. I would think, that beginning
contiguous numbers would be lower odds, as opposed
higher numbers which would have higher odds.

It does not matter whether the numbers are higher or lower.

For sequences of __exactly__ 4, it __does__ matter whether the
sequence is at the end (4-3-2-1 and 49-48-47-46) or in the middle.

However, note that the odds for __a_particular__ end sequence of
__exactly__ 4 is higher, not lower, than for __a_particular__ middle
sequence.

And again, also note that there is __no_difference__ in the odds for
__a_particular__ sequence of __at_least__ 4.

 

[Martin Brown]

To clarify: any of 4 contiguous numbers will have the same odds,
regardless of where being located sequentially at the beginning,
middle, or end of
a number series 1 to 49 as in Lotto 6/49.

Result: =46*COMBIN(45,2) ---> 1 to 45540 odds

It's hard to believe. I would think, that beginning contiguous numbers
would be lower odds, as opposed
higher numbers which would have higher odds.

The lottery depends only on the number of distinct symbols being sampled
- the tags on the balls are only there to make each one unique.

National Lotteries are a voluntary tax on the innumerate poor.

*Except the early version of the Irish Lottery which through a design
flaw of having too many small prizes was defeated by a syndicate.

I have nothing to base this thought on.

That is because it it wrong. The thing that is true is that if you
choose numbers that are mostly above 31 you are more likely to avoid
sharing your winnings with a thousand other birthday date sucker bets.

Tracking number of winners against numbers > 31 and including 13 is
amusing. 42 is also best avoided for similar reasons.

Regards,
Martin Brown

 

[smandula]

Thank You for your Tremendous response.

I appreciate and never expected such a detailed explanation.

Again, With Thanks