This code inserts a new worksheet and deals a 5-Card Poker hand to 10 players. The code requires Excel 2000 or higher. Discussion: The code was written in two ways: as an example of how to use the (VBA) Dictionary Object or a VBA Collection to remove an item (dealt card) from a collection (pack of cards). Code: instructions for use. Input the number of poker players and buy in amount and the calcultor will spit out a points and payout structure for you. It will also let you: Choose the number of tournament payout places or let it calculate it for you. Select a 'base point number' i.e. The number of points the first person out of the tournament gets.

1. Excel Formulas Poker Games
2. Excel Formulas Poker Cheat

Introduction

Excel Formulas Poker Games

Derivations for Five Card Stud

I have been asked so many times how I derived the probabilities of drawing each poker hand that I have created this section to explain the calculation. This assumes some level mathematical proficiency; anyone comfortable with high school math should be able to work through this explanation. The skills used here can be applied to a wide range of probability problems.

The Factorial Function

If you already know about the factorial function you can skip ahead. If you think 5! means to yell the number five then keep reading.

The instructions for your living room couch will probably recommend that you rearrange the cushions on a regular basis. Let's assume your couch has four cushions. How many combinations can you arrange them in? The answer is 4!, or 24. There are obviously 4 positions to put the first cushion, then there will be 3 positions left to put the second, 2 positions for the third, and only 1 for the last one, or 4*3*2*1 = 24. If you had n cushions there would be n*(n-1)*(n-2)* ... * 1 = n! ways to arrange them. Any scientific calculator should have a factorial button, usually denoted as x!, and the fact(x) function in Excel will give the factorial of x. The total number of ways to arrange 52 cards would be 52! = 8.065818 * 10⁶⁷.

The Combinatorial Function

Assume you want to form a committee of 4 people out of a pool of 10 people in your office. How many different combinations of people are there to choose from? The answer is 10!/(4!*(10-4)!) = 210. The general case is if you have to form a committee of y people out of a pool of x then there are x!/(y!*(x-y)!) combinations to choose from. Why? For the example given there would be 10! = 3,628,800 ways to put the 10 people in your office in order. You could consider the first four as the committee and the other six as the lucky ones. However you don't have to establish an order of the people in the committee or those who aren't in the committee. There are 4! = 24 ways to arrange the people in the committee and 6! = 720 ways to arrange the others. By dividing 10! by the product of 4! and 6! you will divide out the order of people in an out of the committee and be left with only the number of combinations, specifically (1*2*3*4*5*6*7*8*9*10)/((1*2*3*4)*(1*2*3*4*5*6)) = 210. The combin(x,y) function in Excel will tell you the number of ways you can arrange a group of y out of x.

Excel Formulas Poker Cheat

Now we can determine the number of possible five card hands out of a 52 card deck. The answer is combin(52,5), or 52!/(5!*47!) = 2,598,960. If you're doing this by hand because your calculator doesn't have a factorial button and you don't have a copy of Excel, then realize that all the factors of 47! cancel out those in 52! leaving (52*51*50*49*48)/(1*2*3*4*5). The probability of forming any given hand is the number of ways it can be arranged divided by the total number of combinations of 2,598.960. Below are the number of combinations for each hand. Just divide by 2,598,960 to get the probability.

Poker Math

The next section shows how to derive the number of combinations of each poker hand in five card stud.

Royal Flush

There are four different ways to draw a royal flush (one for each suit).

Straight Flush

The highest card in a straight flush can be 5,6,7,8,9,10,Jack,Queen, or King. Thus there are 9 possible high cards, and 4 possible suits, creating 9 * 4 = 36 different possible straight flushes.

Four of a Kind

There are 13 different possible ranks of the 4 of a kind. The fifth card could be anything of the remaining 48. Thus there are 13 * 48 = 624 different four of a kinds.

Full House

There are 13 different possible ranks for the three of a kind, and 12 left for the two of a kind. There are 4 ways to arrange three cards of one rank (4 different cards to leave out), and combin(4,2) = 6 ways to arrange two cards of one rank. Thus there are 13 * 12 * 4 * 6 = 3,744 ways to create a full house.

Flush

There are 4 suits to choose from and combin(13,5) = 1,287 ways to arrange five cards in the same suit. From 1,287 subtract 10 for the ten high cards that can lead a straight, resulting in a straight flush, leaving 1,277. Then multiply for 4 for the four suits, resulting in 5,108 ways to form a flush.

Straight

The highest card in a straight can be 5,6,7,8,9,10,Jack,Queen,King, or Ace. Thus there are 10 possible high cards. Each card may be of four different suits. The number of ways to arrange five cards of four different suits is 4⁵ = 1024. Next subtract 4 from 1024 for the four ways to form a flush, resulting in a straight flush, leaving 1020. The total number of ways to form a straight is 10*1020=10,200.

Three of a Kind

There are 13 ranks to choose from for the three of a kind and 4 ways to arrange 3 cards among the four to choose from. There are combin(12,2) = 66 ways to arrange the other two ranks to choose from for the other two cards. In each of the two ranks there are four cards to choose from. Thus the number of ways to arrange a three of a kind is 13 * 4 * 66 * 4² = 54,912.

Two Pair

There are (13:2) = 78 ways to arrange the two ranks represented. In both ranks there are (4:2) = 6 ways to arrange two cards. There are 44 cards left for the fifth card. Thus there are 78 * 6² * 44 = 123,552 ways to arrange a two pair.

One Pair

There are 13 ranks to choose from for the pair and combin(4,2) = 6 ways to arrange the two cards in the pair. There are combin(12,3) = 220 ways to arrange the other three ranks of the singletons, and four cards to choose from in each rank. Thus there are 13 * 6 * 220 * 4³ = 1,098,240 ways to arrange a pair.

Nothing

First find the number of ways to choose five different ranks out of 13, which is combin(13,5) = 1287. Then subtract 10 for the 10 different high cards that can lead a straight, leaving you with 1277. Each card can be of 1 of 4 suits so there are 4⁵=1024 different ways to arrange the suits in each of the 1277 combinations. However we must subtract 4 from the 1024 for the four ways to form a flush, leaving 1020. So the final number of ways to arrange a high card hand is 1277*1020=1,302,540.

Specific High Card

For example, let's find the probability of drawing a jack-high. There must be four different cards in the hand all less than a jack, of which there are 9 to choose from. The number of ways to arrange 4 ranks out of 9 is combin(9,4) = 126. We must then subtract 1 for the 10-9-8-7 combination which would form a straight, leaving 125. From above we know there are 1020 ways to arrange the suits. Multiplying 125 by 1020 yields 127,500 which the number of ways to form a jack-high hand. For ace-high remember to subtract 2 rather than 1 from the total number of ways to arrange the ranks since A-K-Q-J-10 and 5-4-3-2-A are both valid straights. Here is a good site that also explains how to calculate poker probabilities.

Five Card Draw — High Card Hands

Hand	Combinations	Probability
Ace high	502,860	0.19341583
King high	335,580	0.12912088
Queen high	213,180	0.08202512
Jack high	127,500	0.04905808
10 high	70,380	0.02708006
9 high	34,680	0.01334380
8 high	14,280	0.00549451
7 high	4,080	0.00156986
Total	1,302,540	0.501177394

Ace/King High

For the benefit of those interested in Caribbean Stud Poker I will calculate the probability of drawing ace high with a second highest card of a king. The other three cards must all be different and range in rank from queen to two. The number of ways to arrange 3 out of 11 ranks is (11:3) = 165. Subtract one for Q-J-10, which would form a straight, and you are left with 164 combinations. As above there 1020 ways to arrange the suits and avoid a flush. The final number of ways to arrange ace/king is 164*1020=167,280.

Internal Links

For lots of other probabilities in poker, please see my section on Probabilities in Poker.

Written by:Michael Shackleford