Excel as a card shuffler

[animavetus]

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

 

[JMH022]

I took a VB 6.0 workshop at a local tech college (2-day class) to get some basics down. That was years ago, but it helped. I also got the "Using VB 6.0" book from the bookstore and use it constantly as a reference. MrExcel.com is also a wonderful resource I have relied on for years. Good people here!

 

[mcole001140]

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 know it’s been a while since you asked, but maybe I have a novel way of ensuring a foolproof shuffle of a deck.

You can cut the deck, like you’ve said, at a specific place (1-52). Then randomize the cards coming in order from either the 1st half-deck, or the 2nd, using randbetween(0,1).

Then iterate through the two decks, storing your place with a real-number value (a value with a decimal place). The integer value (to the left of the decimal place) can be used to represent cards in the 1st half-deck. The decimal value (to the right of the decimal place) can be used to represent cards in the 2nd half-deck.

When randbetween(0,1) = 0, then increment the integer by 1. When randbetween(0,1) = 1, then increment the decimal place by 0.01. I use 0.01 to ensure that it never gets increments all the way to 0.99, since we’re only using 52 cards.

As you repeat the randomizing, you’ll eventually run into a situation where you run out of cards in one of the half-decks. So just use an If statement to check for this. If you’re out of cards in one half-deck, then there’s no need to randbetween, just choose from the opposite deck (increment the integer or the decimal appropriately). This will take a double-nested If statement.

Once you’ve repeated this randbetween(0,1) 52 times, then use index() and int() functions to determine which card in each half-deck to add to a newly shuffled deck. If the current number is Exactly 1 higher than the previous number, then you know you incremented by 1, which means a card was chosen from the 1st half-deck. If the current number is not Exactly 1 higher, then you know the opposite.

This should produce a shuffled deck every time.

You could set up your columns to allow the final deck to be copy-paste-values into the original so that you could easily shuffle additional rounds.

You could set up additional lookup columns of Suits and Face Values to be concatenated together to produce the desired output of cards