animavetus
New Member
- Joined
- Mar 7, 2011
- Messages
- 4
Hello everybody,
I'm having a bugger of a time building a worksheet/book to model shuffling cards. Thus far, I've concatenated the numbers 2 - 10 or J,Q,K,A (the cards contained within each suit) with H,S,D,C (Hearts, spades... the suits)
From that, i set up a 52 cell column that consists of an ordered set (ordered as a new, unopened deck of cards would be) of cells; i.e. 2h,3h,...
I then created a cell that assigns a "cut position", represented as a number , by using =RANDBETWEEN(14,39) {only 14-39 are included because any position less than 14 or greater than 39 has a less than .5% probability of being chosen as a place to split a 52 card deck for shuffling}.
From the 52 cell column previously mentioned, I created two more columns that represent the two "subdecks" created by the cut using the following two formulae:
=IF(OR(B16=$L$7,B16<$J$14),E16,0)
=IFERROR(VLOOKUP($L$7+B16,$B$16:$E$67,4,FALSE),0)
Problem 1 is that these two formulas do not make a clean array. They are both repeated so that there are 52 potential members of each subdeck, and return a 0 when after the in each cell below the last cell truly representing a "card."
Can I do this part better?
Problem # 2 is more vexing for me.
I have a surprisingly simple, yet accurate, mathematical formula that calculates from which subdeck a card will "fall" when the two subdecks are riffled (riffling is the standard, Las Vegas style method of shuffling). I'll leave it to any curious reader to prove that there is such a formula to themselves.
Ok, so I know how to make a deck of cards. I know, in a clunky way how to make two subdecks. I know how to determine what card would fall next. From this, I need to be able to reduce the size of the subdeck from which the last card fell, recalculate where the next card will come from, then create from that a new 52 cell column that represents the shuffled deck. All this being done with the knowledge that while the two subdecks are pretty realistic, they make a new deck that is constructed from the bottom up.
I hope that is all clear. Am I aming too high?
Thanks,
Anima Vetus
I'm having a bugger of a time building a worksheet/book to model shuffling cards. Thus far, I've concatenated the numbers 2 - 10 or J,Q,K,A (the cards contained within each suit) with H,S,D,C (Hearts, spades... the suits)
From that, i set up a 52 cell column that consists of an ordered set (ordered as a new, unopened deck of cards would be) of cells; i.e. 2h,3h,...
I then created a cell that assigns a "cut position", represented as a number , by using =RANDBETWEEN(14,39) {only 14-39 are included because any position less than 14 or greater than 39 has a less than .5% probability of being chosen as a place to split a 52 card deck for shuffling}.
From the 52 cell column previously mentioned, I created two more columns that represent the two "subdecks" created by the cut using the following two formulae:
=IF(OR(B16=$L$7,B16<$J$14),E16,0)
=IFERROR(VLOOKUP($L$7+B16,$B$16:$E$67,4,FALSE),0)
Problem 1 is that these two formulas do not make a clean array. They are both repeated so that there are 52 potential members of each subdeck, and return a 0 when after the in each cell below the last cell truly representing a "card."
Can I do this part better?
Problem # 2 is more vexing for me.
I have a surprisingly simple, yet accurate, mathematical formula that calculates from which subdeck a card will "fall" when the two subdecks are riffled (riffling is the standard, Las Vegas style method of shuffling). I'll leave it to any curious reader to prove that there is such a formula to themselves.
Ok, so I know how to make a deck of cards. I know, in a clunky way how to make two subdecks. I know how to determine what card would fall next. From this, I need to be able to reduce the size of the subdeck from which the last card fell, recalculate where the next card will come from, then create from that a new 52 cell column that represents the shuffled deck. All this being done with the knowledge that while the two subdecks are pretty realistic, they make a new deck that is constructed from the bottom up.
I hope that is all clear. Am I aming too high?
Thanks,
Anima Vetus