6/49 lotto odds and combin with a difference

[ctaylor11]

Hi, in the bonus ball format of the lottery with numbers from 1 to 49 possibly to be drawn. 6 main numbers are drawn and 1 bonus number. Generally the contestant would pick 6 numbers and then 6 would be drawn plus an additional bonus ball. In this scenario the following odds and combinations are relevant.

<TABLE style="BACKGROUND-COLOR: #ffff00" cellSpacing=2 cellPadding=2 width="100%" align=top border=0><TBODY><TR><TH style="FONT-SIZE: 9pt" height=20>Matches</TH><TH id=bonusOddsHdr style="FONT-SIZE: 9pt" height=20>Odds</TH><TH style="FONT-SIZE: 9pt" height=20>Combinations</TH></TR><TR height=20><TD align=middle><INPUT id=g2_mat[0] readOnly size=1 value=6> </TD><TD><INPUT id=g2_odds[0] readOnly align=center value="1 in 13,983,816"> </TD><TD><INPUT id=g2_comb[0] readOnly align=center size=8 value=1> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[1] readOnly size=1 value=5> +B</TD><TD><INPUT id=g2_odds[1] readOnly value="1 in 2,330,636"> </TD><TD><INPUT id=g2_comb[1] readOnly size=8 value=6> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[2] readOnly size=1 value=5> </TD><TD><INPUT id=g2_odds[2] readOnly value="1 in 55,491"> </TD><TD><INPUT id=g2_comb[2] readOnly size=8 value=252> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[3] readOnly size=1 value=4> +B</TD><TD><INPUT id=g2_odds[3] readOnly value="1 in 44,393"> </TD><TD><INPUT id=g2_comb[3] readOnly size=8 value=315> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[4] readOnly size=1 value=4> </TD><TD><INPUT id=g2_odds[4] readOnly value="1 in 1,057"> </TD><TD><INPUT id=g2_comb[4] readOnly size=8 value=13,230> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[5] readOnly size=1 value=3> +B</TD><TD><INPUT id=g2_odds[5] readOnly value="1 in 2,436"> </TD><TD><INPUT id=g2_comb[5] readOnly size=8 value=5,740> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[6] readOnly size=1 value=3> </TD><TD><INPUT id=g2_odds[6] readOnly value="1 in 58"> </TD><TD><INPUT id=g2_comb[6] readOnly size=8 value=241,080> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[7] readOnly size=1 value=2> +B</TD><TD><INPUT id=g2_odds[7] readOnly value="1 in 325"> </TD><TD><INPUT id=g2_comb[7] readOnly size=8 value=43,050> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[8] readOnly size=1 value=2> </TD><TD><INPUT id=g2_odds[8] readOnly value="1 in 8"> </TD><TD><INPUT id=g2_comb[8] readOnly size=8 value=1,808,100> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[9] readOnly size=1 value=1> +B</TD><TD><INPUT id=g2_odds[9] readOnly value="1 in 104"> </TD><TD><INPUT id=g2_comb[9] readOnly size=8 value=134,316> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[10] readOnly size=1 value=1> </TD><TD><INPUT id=g2_odds[10] readOnly value="1 in 2"> </TD><TD><INPUT id=g2_comb[10] readOnly size=8 value=5,641,272> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[11] readOnly size=1 value=0> +B</TD><TD><INPUT id=g2_odds[11] readOnly value="1 in 99"> </TD><TD><INPUT id=g2_comb[11] readOnly size=8 value=141,778> </TD></TR><TR><TD align=middle><INPUT id=g2_mat[12] readOnly size=1 value=0> </TD><TD><INPUT id=g2_odds[12] readOnly value="1 in 2"> </TD><TD><INPUT id=g2_comb[12] readOnly size=8 value=5,954,676></TD></TR></TBODY></TABLE>

Now say for argument sake all the above information remains the same except that instead of me picking 6 numbers, I only pick 4 numbers. How would I use excel to calculate the odds of all of the 4 numbers i picked appearing in the 7 numbers drawn (6 main numbers + 1 bonus number). Once the odds have been calculated in there a way for excel to generate all possible combinations (order of the numbers is not specific and can appear in any order) ? Thanks your assistance is greatly appreciated.

 

[shg]

Welcome to the board.

The chance of the first ball matching one of the 7 winners is 7/49; the chance of the second is 6/48, the third 5/47, and the fourth is 4/46. So the chance of all matching is =7*6*5*4/49*48*47*46 = 1/6053.6

Two ways to get there:

      -A-- --B-- ---C---- ---D----
  1   Nums Balls Odds(1)  Odds(2) 
  2      7    49        7        7
  3      6    48       56       56
  4      5    47    526.4    526.4
  5      4    46   6053.6   6053.6
  6      3    45    90804    90804
  7      2    44  1997688  1997688
  8      1    43 85900584 85900584

In C2 and down, confirmed with Ctrl+Shift+Enter:

=PRODUCT(B$2:B2/A$2:A2)

Or in D2 and down, entered normally:

=COMBIN(49, 7+1-A2) / COMBIN(7, 7+1-A2)

 

[ctaylor11]

Thanks very much for the help, much appreciated