Purpose
Return value
Syntax
=MULTINOMIAL(number1,[number2],...)
- number1 - The first value.
- number2 - [optional] Additional values.
Using the MULTINOMIAL function
The Excel MULTINOMIAL function calculates the multinomial coefficient, which is used to determine the number of ways to assign groups of items into specified sizes. This is especially useful in combinatorics and probability, where you need to count the distinct ways to distribute items into multiple groups, regardless of the order within each group.
Key features
Returns the multinomial coefficient for a set of numbers
Accepts 1 to 255 arguments
All arguments must be non-negative numbers
Returns #VALUE! if any argument is non-numeric
Returns #NUM! if any argument is negative
Key features
Example #1 - Basic usage
Example #2 - Drawing marbles from a bag
Example #3 - Count possible combinations
Example #4 - Calculate probability
Example #5 - Errors
When to use
Formula
Example #1 - Basic usage
The formula below calculates the multinomial coefficient for the numbers 3, 6, and 1:
=MULTINOMIAL(3, 6, 1) // returns 840
This is equivalent to:
=FACT(3+6+1)/(FACT(3)*FACT(6)*FACT(1))
Example #2 - Drawing marbles from a bag
Suppose you have a bag containing 2 red marbles and 2 blue marbles. You draw all 4 marbles one by one and place them in order. We can use the MULTINOMIAL function to count the number of different sequences of colors that could be drawn.
=MULTINOMIAL(2, 2) // returns 6
This counts the number of different color sequences possible when drawing 2 red and 2 blue marbles. The 6 possible sequences are:
- Red, Red, Blue, Blue
- Red, Blue, Red, Blue
- Red, Blue, Blue, Red
- Blue, Red, Red, Blue
- Blue, Red, Blue, Red
- Blue, Blue, Red, Red
Example #3 - Count possible combinations
The MULTINOMIAL function is useful when you have more than two groups and want to count the number of distinct ways to distribute items into groups of specific sizes.
For example, suppose you survey 9 people about their favorite ice cream flavor, and the choices are either chocolate, vanilla, or strawberry. “How many different ways could 2 people pick chocolate, 3 pick vanilla, and 4 pick strawberry?”
We can use the MULTINOMIAL function to count the number of distinct ways to distribute the 9 people into groups of 2, 3, and 4. The formula is:
=MULTINOMIAL(2, 3, 4) // returns 1260
Example #4 - Calculate probability
The MULTINOMIAL function can also be used to calculate probabilities in multinomial distributions. Excel does not provide a dedicated function for the multinomial distribution, so we need to construct the formula manually.
Continuing from the previous example, suppose the probability that a person chooses chocolate is 0.5, vanilla is 0.35, and strawberry is 0.15. Suppose you want to know: “If you survey 9 people, what is the probability that 2 choose chocolate, 3 choose vanilla, and 4 choose strawberry?”
To start, let’s calculate the probability of just one specific way to assign people to flavors: for example, the first two people pick chocolate, the next three pick vanilla, and the last four pick strawberry:
=(0.5^2) * (0.35^3) * (0.15^4) // 7.75195E-06
This value is quite small, because it only accounts for one possible way the outcome could occur. In reality, there are 1260 different ways to assign 2, 3, and 4 people to the three flavors, and each assignment is equally likely. We need to count all possible assignments using the MULTINOMIAL function, and then multiply by the probability of each outcome.
=MULTINOMIAL(2,3,4) * (0.5^2) * (0.35^3) * (0.15^4) // 0.006837223
This gives us the probability that, for our survey of 9 people, 2 choose chocolate, 3 choose vanilla, and 4 choose strawberry.
Example #5 - Errors
All arguments for the MULTINOMIAL function must be numeric and non-negative. If any argument is negative, the function returns #NUM! error.
=MULTINOMIAL(-3, 6, 1) // returns #NUM!
If any argument is non-numeric, the function returns #VALUE! error.
=MULTINOMIAL(3, "a", 1) // returns #VALUE!
When to use
Excel offers several functions for combinatorics and probability. For example, COMBIN and PERMUT can be used for specific types of counting problems involving two groups.
- COMBIN is used for counting combinations of items where order does not matter.
- PERMUT is used for counting permutations of items where order does matter.
The MULTINOMIAL function is more general and is used when you have more than two groups and want to count the number of distinct ways to distribute items into groups of specific sizes, when the order within each group does not matter.
The multinomial coefficient is closely related to the multinomial distribution, which describes the probabilities of counts for more than two possible outcomes (generalizing the binomial distribution). However, Excel does not provide a dedicated function for the multinomial distribution. If you need to calculate multinomial probabilities, you will need to construct the formula manually using the MULTINOMIAL function together with probability terms.
Formula
The MULTINOMIAL function calculates the multinomial coefficient, which is the ratio of the factorial of the sum of the arguments to the product of the factorials of each argument. The formula is:
Purpose
Return value
Syntax
=MUNIT(dimension)
- dimension - An integer for the size of the unit matrix.
Using the MUNIT function
The MUNIT function returns a unit matrix for a given dimension, n , with a size of n x n . The unit matrix is also called the identity matrix. The MUNIT function takes just one argument, dimension , which should be a positive integer. The resulting matrix contains ones on the main diagonal and zeros in every other position.
Examples
The formula in D5 returns a 3 x 3 unit matrix :
=MUNIT(3) // 3 x 3
With 3 given for dimension, the MUNIT function returns a 3 by 3 array like this:
| 1 | 0 | 0 |
|---|---|---|
| 0 | 1 | 0 |
| 0 | 0 | 1 |
The formula in J5 returns a 5 x 5 unit matrix.
=MUNIT(5) // 5 x 5
With 5 given for dimension, the MUNIT function returns a 5 by 5 array like this:
| 1 | 0 | 0 | 0 | 0 |
|---|---|---|---|---|
| 0 | 1 | 0 | 0 | 0 |
| 0 | 0 | 1 | 0 | 0 |
| 0 | 0 | 0 | 1 | 0 |
| 0 | 0 | 0 | 0 | 1 |
Array syntax
The MUNIT function returns an array of values. In Excel 365 , where dynamic arrays are native , you can use the MUNIT function without any special handling – MUNIT will return an array of values that spill directly into cells in the worksheet.
In versions of Excel prior to Excel 365, you need to enter MUNIT enter as a multi-cell array formula to display results directly on the worksheet. To do this, make a selection of the right size, and enter MUNIT with control + shift + enter .
Notes
- Dimension must be greater than zero.
- If dimension is zero or less than zero, MUNIT will return the #VALUE error.
- A unit matrix is also called an identity matrix .
