Table of Contents
The Multinomial Distribution can be used in Excel to calculate the probability of a certain number of successes in a given number of independent trials. It is done using the Excel function MULTINOMIAL, which takes as its arguments the number of trials and the probabilities of success for each category. The output is a probability that the given combination of successes will occur. This is very useful in data analysis and can provide a better understanding of the likelihood of certain events.
The describes the probability of obtaining a specific number of counts for k different outcomes, when each outcome has a fixed probability of occurring.
If a X follows a multinomial distribution, then the probability that outcome 1 occurs exactly x1 times, outcome 2 occurs exactly x2 times, etc. can be found by the following formula:
Probability = n! * (p1x1 * p2x2 * … * pkxk) / (x1! * x2! … * xk!)
where:
- n: total number of events
- x1: number of times outcome 1 occurs
- p1: probability that outcome 1 occurs in a given trial
The following examples show how to calculate multinomial probabilities in Excel.
Example 1
In a three-way election for mayor, candidate A receives 10% of the votes, candidate B receives 40% of the votes, and candidate C receives 50% of the votes.
If we select a random sample of 10 voters, what is the probability that 2 voted for candidate A, 4 voted for candidate B, and 4 voted for candidate C?
The following screenshot shows how to calculate this probability in Excel:
The probability that exactly 2 people voted for A, 4 voted for B, and 4 voted for C is 0.0504.
Example 2
Suppose an urn contains 6 yellow marbles, 2 red marbles, and 2 pink marbles.
If we randomly select 4 balls from the urn, with replacement, what is the probability that all 4 balls are yellow?
The following screenshot shows how to calculate this probability in Excel:
The probability that all 4 balls are yellow is 0.1296.
Example 3
Suppose two students play chess against each other. The probability that student A wins a given game is 0.5, the probability that student B wins a given game is 0.3, and the probability that they tie in a given game is 0.2.
If they play 10 games, what is the probability that player A wins 4 times, player B wins 5 times, and they tie 1 time?
The following screenshot shows how to calculate this probability in Excel:
The probability that player A wins 4 times, player B wins 5 times, and they tie 1 time is about 0.038.
The following tutorials provide additional information about the multinomial distribution: