A to Z of Excel Functions: The MULTINOMIAL Function

The multinomial distribution is a generalisation of the binomial distribution to two or more events.

In probability theory and statistics, the binomial distribution with parameters n and p is the discrete probability distribution of the number of successes in a sequence of n independent success / failure experiments, each of which yields success with probability p. For the record, a success / failure experiment is also called a Bernoulli experiment or Bernoulli trial.  The binomial distribution is frequently used to model the number of successes in a sample of size n drawn with replacement from a population of size N.

This function returns the individual term binomial distribution probability.  The BINOM.DIST function should be used in problems with a fixed number of tests or trials, when the outcomes of any trial are only success or failure, when trials are independent and when the probability of success is constant throughout the experiment.  For example, BINOM.DIST can calculate the probability that two of the next three babies born are male.

Therefore, for an experiment with the following characteristics:

• the experiment consists of n independent trials
• each trial has k mutually exclusive outcomes E_i
• for each trial the probability of outcome E_i is p_i,

let x₁, …, x_k be discrete random variables whose values are the number of times outcome E_i occurs in n trials.  Then, x₁, …, x_k has a multinomial distribution.  The (joint) probability distribution function (pdf) is defined as follows:

where  

The case where k = 2 is equivalent to the binomial distribution.

Key properties of the multinomial distribution are:

• E[x_i] = np_i
• var(x_i) = np_i(1–p_i)
• cov(x_i, x_j) = –np_ip_j for i ≠ j.

The MULTINOMIAL function in Excel returns the ratio of the factorial of a sum of values to the product of factorials.  It employs the following syntax to operate:

The MULTINOMIAL function has the following arguments:

• number1, number2, …: number1 is required (the rest are optional), and represents one [1] to 255 values for which you require the multinomial.

It should be noted that:

• if any argument is nonnumeric, MULTINOMIAL returns the #VALUE! error value
• if any argument is less than zero, MULTINOMIAL returns the #NUM! error value
• the multinomial is:

As an example, suppose that a bag contains 12 balls: five [5] red, four [4] yellow and three [3] blue (5 + 4 + 3 = 12).  You reach in the bag and pull out a ball at random and then replace before making a subsequent selection.  This experiment is repeated a total of 10 times.  What is the probability that the outcome will result in exactly six [6] reds, two [2] yellows and two [2] blues (6 + 2 + 2 = 10)?

The possible outcomes for each trial in this experiment are E₁ = a red ball is drawn, E₂ = a yellow ball is drawn and E₃ = a blue ball is drawn.  Thus p₁ = 5/12, p₂ = 4/12, p₃ = 3/12, x₁ = 6, x₂ = 2 and x₃ = 2, and so

This has been reproduced in Excel using the MULTINOMIAL function (using three alternative approaches) below:  

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.

A full page of the function articles can be found here.