# A to Z of Excel Functions: The MULTINOMIAL Function

5 September 2022

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**is_{i}**p**,_{i}

let **x _{1}**, …,

**x**be discrete random variables whose values are the number of times outcome

_{k}**E**occurs in

_{i}**n**trials. Then,

**x**, …,

_{1}**x**has a

_{k}**distribution. The (joint) probability distribution function (pdf) is defined as follows:**

*multinomial*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**for_{i}p_{j}**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:

**MULTINOMIAL(number1, [number2, …])**

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 _{1}** = a red ball is drawn,

**E**= a yellow ball is drawn and

_{2}**E**= a blue ball is drawn. Thus

_{3}**p**= 5/12,

_{1}**p**= 4/12,

_{2}**p**= 3/12,

_{3}**x**= 6,

_{1}**x**= 2 and

_{2}**x**= 2, and so

_{3}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.*