Poker challenge in Excel

posted Aug 25, 2012, 2:01 PM by r   [ updated Aug 27, 2012, 2:26 AM ]
There is an interesting challenge in the Excel Hero LinkedIn group:
The ultimate poker challenge!

Here we describe some possible solutions for two sub-challenge: the card shuffling challenge and the deal challenge.


You can try to find another solutions, the two Excel files below could be a good basis of the challenge - please feel free to use it!
(Download at the bottom of the page by clicking on the small arrow on the right-hand side.)

The shuffling challenge:
The formula in column "A" creates an array of numbers from 1 to 52 arranged randomly.
(This is the formula we were talking about in the Numbers arranged randomly challenge but you can read about it here too.)

You need only one input value: tot_cards is the total number of cards - in this case it is 52.

=SMALL(IF(1-COUNTIF(A$1:A1,ROW(INDIRECT("1:"&tot_cards))),ROW(INDIRECT("1:"&tot_cards))),
INT(RAND()*(tot_cards-COUNT(A$1:A1)))+1)

Array-enter this formula to cell A2 and copy down.


The deal challenge:
To simulate the deal, we can use the first 5 cards as the first hand, the second five as the second hand.
The deal challenge is to read these 5-element groups and sort them by the value of the card, then by suits. In the files below you can see two different formulas array-entered into the range D4:G8.
We use the name "cards" for the deck range created above, and "n" to store the number of cards in play - in the below cases n=5.

The first solution (by r):

=INDEX(cards,MATCH(SMALL(MOD(INDEX(cards,(COLUMN()-COLUMN(D4))*n+1):INDEX(cards,(COLUMN()-COLUMN(D4)+1)*n)-1,tot_cards/4)+INDEX(cards,(COLUMN()-COLUMN(D4))*n+1):INDEX(cards,(COLUMN()-COLUMN(D4)+1)*n)%,ROW(1:5)),MOD(cards-1,tot_cards/4)+cards%,))

The second solution (by Kris):

=--RIGHT(INDEX(SMALL(INT((ROW(cards)-CELL("row",cards))/n)*100+MOD(cards-1,tot_cards/4)+cards%,ROW(1:20)),ROW(1:5)+(TRANSPOSE(ROW(1:5))-1)*n,)%,2)

It is not as general as the solution above, but contains a little trick. The basic idea is to combine the keys together, in other words, to add the values which we use as sort criterion. On top of that, we should to take into account that we need to group the cards according the the hands. So this must be the first sort level, and we need the trick here: let's add the number of hand to the combined key!

Adding the keys would work only in case we make sure the values could not mix - in the particular case we could use that Excel stores numbers in 15 digits, so we can separate the different sort levels by multiplying the powers of 10.

The first sort level is the hand-index:
 SMALL(INT((ROW(cards)-CELL("row",cards))/n)*100
the second level is the value of the card:
 MOD(cards-1,tot_cards/4)
and the third level is the suit - actually the numbering order of the cards represent the suit, so it is enough to add this value to the key:
 cards

Now we could sort it with the help of
SMALL.
INDEX formula will help to create the requested two-dimension array. We use the row parameter only, and put a two-dimension matrix there!

Only one thing left: to get the card indexes. In this case the easiest way is to use RIGHT - the last two digit is the card number what we need.

by Frankens Team
Ĉ
r,
Aug 27, 2012, 1:52 AM
Ĉ
r,
Aug 25, 2012, 2:01 PM
Comments