CODEWITHSUNDEEP
excelexcel-formula
lexus1989
lexus1989

Reputation: 3

Excel- Generating a set of numbers with normal distribution with MIN and MAX

I want to generate a single column of 6000 numbers with a normal distribution, with a mean of 30.15, standard deviation of 49.8, minium of -11.5, maximum 133.5.

I am a total newb at this so i tried to use the following formula in a cell and than just drag it down to cell 6000:

=NORMINV(RANDBETWEEN(-11.5,133.5)/100,30.15,49.8)

It returns a value but sometimes it returns #NUM! error. Thank you!

Upvotes: 0

Views: 1895

Answers (1)

Bathsheba
Bathsheba

Reputation: 234715

Unfortunately NORMINV expects a probability for the argument, which must be a value in the interval (0, 1). Any parameter outside that range will yield #NUM!.

What you're asking cannot be done directly with a normal distribution since that has no constraints on the minimum and maximum values.

One approach is to use a primary column to generate the normally distributed numbers, then filter out the ones you want in the adjacent column. But this will cause even the mean (let alone higher moments) to go off quite considerably due to your minimum and maximum values not being equidistant from the mean. You could get round this by recentering the distribution and adjusting afterwards.

Upvotes: 1

Related Questions