CODEWITHSUNDEEP
excelrandom
Nikolai Kahnevits
Nikolai Kahnevits

Reputation: 23

How to generate a randomized range of numbers that sums into end parameter

I have a spreadsheet with a certain parameters: Range start: 1 Range end: 100 Randomization: N

Then there is a list with a size of 5. Excel generates me numbers with a step of 20 in each cell of that list. And if list size is changed, the step size will also change accordingly

I am trying to randomize every entry by +- of N so that an end sum would still be 100. Is there an elegant solution?

Upvotes: 2

Views: 69

Answers (1)

Severin Pappadeux
Severin Pappadeux

Reputation: 20080

Well, canonical way to sample such random number is to apply Multinomial distribution. It is automatically makes sum equal to desired number.

In your case, n = 100, k = 5, pi = 1/5 = 0.2. I know Excel provides function MULTINOMIAL(x1, ..., xk) = n!/(x1!*...*xk!) so probabilities could be computed with easy (see http://www.real-statistics.com/binomial-and-related-distributions/multinomial-distribution/). I don't know how to do sampling in Excel, perhaps stats package for Excel has desired functionality.

UPDATE

There is Excel sampling code right in the wiki page on multinomial, but I think it would be slow

Upvotes: 1

Related Questions