|P.Mean: Generating multinomial random variables in Excel (created 2009-11-23).|
Someone asked how to generate six random integers subject to the conditions that the sum of those random integers had to equal a value, x. This is a classic description of a multinomial distribution. Unstated in the question, but assumed by me, was that each random integer had to have the same distribution. that forces the probability vector for the multinomial to be (1/6, 1/6, 1/6, 1/6, 1/6, 1/6).
Microsoft Excel does not have a sophisticated system for generating random variables. You can't just generate a random multinomial vector, you can even generate a binomial random variable. But there is a very simple approach, inspired by the Wikipedia page on the multinomial distribution,
that works very nicely with Excel. Generate x random variables which are uniform on the integers 1 through 6. The place the number of those random variables equal to 1 in cell A1, equal to 2 in cell A2, and so forth. This insures a common distribution for cells A1 through A6 and forces the sum to be exactly x.
I've worked out a simple spreadsheet with x=10.
This work is licensed under a Creative Commons Attribution 3.0 United States License. This page was written by Steve Simon and was last modified on 2017-06-15. Need more information? I have a page with general help resources. You can also browse for pages similar to this one at Category: Unusual data.