Select Page

# Challenge of The Month

 Solve an Excel Challenge and WIN A PRIZE! Check out our challenge of the month and see what you can do! We periodically post a challenge to our Excel user community. Whoever successfully solves the problem wins a cool MrExcel.com prize and bragging rights! What more could you ask for?

Challenge for for April/May 2013: Perfect Shuffle

A “perfect shuffle” is a trick used by card sharks and magicians to give the illusion of randomness when in fact the order of the cards is being controlled. It involves cutting the deck into two exactly equal halves, and then perfectly interlacing the two halves one card at a time.For example, if the deck contains 52 cards numbers in order 1 to 52, the deck will be cut into a left half (numbered 1-26) and a right half (containing 27-52). They are merged, from the bottom up, starting with the bottom right card (52), then the bottom left card (26), then the next bottom right card (51), then card 25, 50, and so on. The deck will be in the order of 1, 27, 2, 28, …, 25, 51, 26, 52.Interesting fact: if you have a deck of 52 cards and repeat the perfect shuffle 7 more times, the deck will be back in the original starting order! So, for a deck of 52 cards, 8 perfect shuffles will leave the deck in the original order.Challenge: Build an Excel workbook that will model a perfect shuffle for every even-size deck from 2 to 200 cards. What is the smallest number of perfect shuffles required to get the deck back in the original order?For 4 cards, the answer is 2. For 52 cards, the answer is 8. What about the other deck sizes? Try to find the simplest and most elegant way of modelling this that you can. Try also to find interesting ways to display the answers and highlight any patters you may see emerging.Bonus points if you can spot any pattern. Is there any way to model this for a deck size of 20,000 cards?Prizes: Win a \$99 bundle of Excel e-books.

Prizes awarded for:

• Best Non-VBA solution
• Best Solution
• Most Interesting

Dates: April 24, 2013 through May 22, 2013. Original video announcing the contest:

ResultsThe winners are:

• Daniel Dion for the best non-VBA answer
• Leo Meijer for most interesting
• Alex Gordon for best VBA answer and overall best solution

Thanks to Dan Mayoh for proposing the challenge and for judging the competition.Dan provided some notes about the entries:
Non VBA answers up to 200 cards:A common way to model this problem for a deck size up to 200 was with a 200*200 array, with the first column being the order of the deck after 1 shuffle, the second column being the order of the deck after the second shuffle, and so on. Doing this involves two steps:First is finding a formula that performs one ‘shuffle’, that is looks at the order of cards one column to the left, and rearranges it for the current column. This formula is then dragged across all columns. This was done in a few different ways. Most people (myself included) did this in a direct manner, using some sort of INDEX and OFFSET combination to pick up the correct number from the right spot in the previous deck. A couple of people used what I think is a very clever idea and first used some very simple functions to make a ‘reference column’, equal to the deck after 1 shuffle. Then, for all subsequent shuffles, they use a much simpler formula (no OFFSETS) that looks at the value of the reference column for a given row, and returns the card in that value’s place from the previous column. Alex Gordon’s entry was the best example of this.Example 1: Daniel Dion’s formula to do the shuffle:
Example 2: Alex Gordon set up a reference column to do the shuffle:

In Alex’s workbook, cell E6 contains =1+E4 and this is copied down. The formula in H4 is = INDEX(G\$4:G\$1003, \$E4) * \$C4 and is copied down and across.The second step is to then check whether the current column matches the original starting order. If it does, you take note of how many shuffles this took to occur, and you’re done. The best way to do this is with a single array formula taking up just one cell above the column. Daniel Dion did this very well. Some people performed this step but checked only the location of the ‘2’ card, and deciding that when it is back in its original spot, the whole deck is in order. As it turns out, this will give the right answer, but a fair bit of mathematics is required to demonstrate that. And if the 2 card is all you are checking in this second step, then it is a waste of resources to model the shuffling of every card in the deck. A third approach to do this second step was to utilise a second worksheet with another 200*200 array, but this is far less efficient than an array formula.

Here is Alex Gordon’s chart showing the number of shuffles required for different sized decks. There is not a particular pattern that is apparent.

#### MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations. This site contains affiliate links. Any affiliate commissions that we earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com running. You can earn a commission for sales leads that you send to us by joining our affiliate program. View my Privacy Policy.

Excel ® is a registered trademark of the Microsoft Corporation.

MrExcel ® is a registered trademark of Tickling Keys, Inc.