## Spreadsheets

- For dice, here's the BINOMDIST calculator.
- For cards, here's the HYPGEOMDIST calculator.

## How it's done

Dice probabilities are calculated using something called the

**Binomial Distribution**. Card deck probabilities are calculated using the**Hypergeometric Distribution**.## Dice/Binomial

The binomial distribution is used to calculate the probability of repeated independent trials whose individual probability of success is know. And dice are the intuitive example of this, because we know there is a 1/6 probability of a die rolling a 6, and we want to know the probability of rolling 2 sixes with 7 dice.For this problem, the spreadsheet binomdist formula lets us calculate either the probability of rolling exactly 2 sixes or the cumulative probability of 2 or fewer sixes, which is nice. All we have to do is this:

For exactly 2 sixes in 7 dice, probability

=BINOMDIST(num_successes, num_trials, prob_success, cumulative)

=BINOMDIST(2, 7, 1/6, FALSE)

=23.44%

For 2 or fewer sixes in 7 dice, probability

=BINOMDIST(num_successes, num_trials, prob_success, cumulative)

=BINOMDIST(2, 7, 1/6, TRUE)

=90.42%

For 2 or more sixes in 7 dice, we have to think slightly harder. We can either add the exact probabilities of 2, 3, 4, 5, 6, and 7 sixes in 7 dice, or we can find the probability of failing to get a six on 5 or fewer dice. So probability

=BINOMDIST(num_successes, num_trials, prob_success, cumulative)

=BINOMDIST(7-2, num_trials, 1-1/6, cumulative)

=BINOMDIST(5, 7, 5/6, TRUE)

=33.02%

And just to check our math, we make sure there is a 100% probability of getting either 2 or fewer sixes or 3 or more sixes. Probability

=BINOMDIST(num_successes, num_trials, prob_success, cumulative)+BINOMDIST(num_successes, num_trials, prob_success, cumulative)

=BINOMDIST(num_successes, num_trials, prob_success, cumulative)+BINOMDIST(7-3, num_trials, 1-1/6, cumulative)

=BINOMDIST(2, 7, 1/6, TRUE)+BINOMDIST(4, 7, 5/6, TRUE)

=100%

=BINOMDIST(num_successes, num_trials, prob_success, cumulative)+BINOMDIST(7-3, num_trials, 1-1/6, cumulative)

=BINOMDIST(2, 7, 1/6, TRUE)+BINOMDIST(4, 7, 5/6, TRUE)

=100%

## Cards/hypergeometric

The hypergeometric distribution is used to calculate the probability of drawing an exact number of successes from a population where the number of successes in the population is known, and the draws are not replaced between draws. And cards are an intuitive example of this, because we know there are 13 hearts in a 52-card deck, and we want to know the probability of there being 4 hearts in a 7-card deal/draw.

For this problem, the spreadsheet HYPGEOMDIST formula lets us calculate only probability of drawing exactly 4 hearts in a 7-card deal/draw. Probability

=HYPGEOMDIST(num_successes, num_draws, successes_in_pop, pop_size)

=HYPGEOMDIST(4, 7, 13, 52)

=4.88%

If we want to know the probability of 4 "or more" hearts in a 7-card deal/draw, we have to add the probabilities for 4, 5, 6, and 7 hearts, which

=HYPGEOMDIST(4, 7, 13, 52)

+HYPGEOMDIST(5, 7, 13, 52)

+HYPGEOMDIST(6, 7, 13, 52)

+HYPGEOMDIST(7, 7, 13, 52)

+HYPGEOMDIST(5, 7, 13, 52)

+HYPGEOMDIST(6, 7, 13, 52)

+HYPGEOMDIST(7, 7, 13, 52)

=5.65% Not a lot more than drawing exactly 4 hearts.

Checking our math again, we calculate all the probabilities of hearts in our hand:

=HYPGEOMDIST(0, 7, 13, 52)

+HYPGEOMDIST(1, 7, 13, 52)

+HYPGEOMDIST(2, 7, 13, 52)

+HYPGEOMDIST(3, 7, 13, 52)

+HYPGEOMDIST(4, 7, 13, 52)

+HYPGEOMDIST(5, 7, 13, 52)

+HYPGEOMDIST(6, 7, 13, 52)

+HYPGEOMDIST(7, 7, 13, 52)

+HYPGEOMDIST(1, 7, 13, 52)

+HYPGEOMDIST(2, 7, 13, 52)

+HYPGEOMDIST(3, 7, 13, 52)

+HYPGEOMDIST(4, 7, 13, 52)

+HYPGEOMDIST(5, 7, 13, 52)

+HYPGEOMDIST(6, 7, 13, 52)

+HYPGEOMDIST(7, 7, 13, 52)

=100%

## No comments:

Post a Comment