Massive Probability Calcs

[R4M3N]

I am creating a spreadsheet with a heavy amount of probability calcs in it. I am aiming to do calculations for card drawing (specifically for MtG). I have wrestled out a hard way to do some of the math, and it turns out putting it into a spread sheet is still difficult. So there are two great ways someone could help me. Either I would like to know the elegant statistical way to solve this problem, or the way to make Excel assist me with my more brute force approach.

I want to know the odds of a card being on the top of a deck. There is a variable number of copies, and it could be in your starting hand. I completely understand how the probability will work out, but I am doing a calculation for each possible event, accounting for drawing every card (60 total). Counting out the starting hand (7 cards), that leaves 53 independent formulas where I would have to have every possible situation represented. If anyone knows the clever, statistical way to approach this, I'm all ears.

In making Excel do the leg work, I encountered the following issue:
[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]7.55%[/TD]
[TD="align: center"]7.69%[/TD]
[TD="align: center"]7.84%[/TD]
[TD="align: center"]8.00%[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]=A1[/TD]
[TD="align: center"]=(1-A1)*B1[/TD]
[TD="align: center"]=(1-A1)*(1-B1)*C1[/TD]
[TD="align: center"]=(1-A1)*(1-B1)*(1-C1)*D1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to make this happen automatically, or somewhat automatically? This shows 4 draw, and I'll have 53. Then I have to do this 3 more times, then 3 times all over, then 2 etc. I wanted to use a range where one end was a relative ref and the other was fixed, but I didn't know how to get the extra multiplication terms to appear.

Thanks for any help you can offer!

Edit: I would prefer to use only Excel, but I was versed in VBA once upon a time. I've yet to dust off those skills recently, but if that's the only way, then I suppose it's time...

 

[R4M3N]

Oh, and I'm using Excel 2010 and Widows 7.

 

[mortgageman]

I am creating a spreadsheet with a heavy amount of probability calcs in it. I am aiming to do calculations for card drawing (specifically for MtG). I have wrestled out a hard way to do some of the math, and it turns out putting it into a spread sheet is still difficult. So there are two great ways someone could help me. Either I would like to know the elegant statistical way to solve this problem, or the way to make Excel assist me with my more brute force approach.

I want to know the odds of a card being on the top of a deck. There is a variable number of copies, and it could be in your starting hand. I completely understand how the probability will work out, but I am doing a calculation for each possible event, accounting for drawing every card (60 total). Counting out the starting hand (7 cards), that leaves 53 independent formulas where I would have to have every possible situation represented. If anyone knows the clever, statistical way to approach this, I'm all ears.

In making Excel do the leg work, I encountered the following issue:
[TABLE="class: grid, width: 600, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]7.55%
[/TD]
[TD]7.69%
[/TD]
[TD]7.84%
[/TD]
[TD]8.00%
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]=A1
[/TD]
[TD]=(1-A1)*B1
[/TD]
[TD]=(1-A1)*(1-B1)*C1
[/TD]
[TD]=(1-A1)*(1-B1)*(1-C1)*D1
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Is there a way to make this happen automatically, or somewhat automatically? This shows 4 draw, and I'll have 53. Then I have to do this 3 more times, then 3 times all over, then 2 etc. I wanted to use a range where one end was a relative ref and the other was fixed, but I didn't know how to get the extra multiplication terms to appear.

Thanks for any help you can offer!

Edit: I would prefer to use only Excel, but I was versed in VBA once upon a time. I've yet to dust off those skills recently, but if that's the only way, then I suppose it's time...

First of all - why 60? Where did that come from? Second of all, what game are you playing - poker, blackjack etc?

Gene

 

[R4M3N]

MtG, Magic the Gathering. It's a trading card game. 60 is the minimum deck size.

 

[mortgageman]

So it is not a standard deck. You might want to let everyone know the configuration of this MtG Deck. What are the "suits", the numbers, faces, whatever.

Gene

 

[shg]

From your example, it appears there are four copies of each rank. The probability of a given rank appearing at the top of the deck at any point depends on both the number of cards remaining and the number of cards of that rank remaining. So it seems to me it should be like this:

       ----A---- --B-- --C-- --D-- --E--
   1   Deck         60                  
   2                                    
   3   Position    4     3     2     1  
   4           1 6.67% 5.00% 3.33% 1.67%
   5           2 6.78% 5.08% 3.39% 1.69%
   6           3 6.90% 5.17% 3.45% 1.72%
   7           4 7.02% 5.26% 3.51% 1.75%
   8           5 7.14% 5.36% 3.57% 1.79%
   9           6 7.27% 5.45% 3.64% 1.82%
  10           7 7.41% 5.56% 3.70% 1.85%
  11           8 7.55% 5.66% 3.77% 1.89%
  12           9 7.69% 5.77% 3.85% 1.92%
  13          10 7.84% 5.88% 3.92% 1.96%

The formula in B5 and copied across and down is

=($B$2 - B$4) / ($B$1 - $A5 + 1)