How can I get a Truncated Normal Distribution (using NORMINV) ?

[Garry Doyle]

I need some help. Is there any way that I can truncate the values
generated by the NORMINV function so that they are limited to a specific
range?

For example, if I have a normal distribution with a std. dev of 5 and a
mean of 35 and I generate a probability using RAND() and I plug them into
the NORMINV function it returns a value (which is dependent on the value
generated by RAND because the std. dev and mean are constant). But say I
want to limit the value returned by NORMINV to be within a certain range (a
min of 15 and a max of 60). Is there any way of setting the spreadsheet up
so that it recalculates RAND until the value returned by NORMINV is within
that set range of values?

I tried using data validation to do this but with no luck.

I hope I wasn't too confusing in explaining my problem. Any suggestions
would be appreciated.

[Mike Middleton]

Garry Doyle -

> I need some help. Is there any way that I can truncate the values
> generated by the NORMINV function so that they are limited to a specific
> range?
>
> For example, if I have a normal distribution with a std. dev of 5 and a
> mean of 35 and I generate a probability using RAND() and I plug them into
> the NORMINV function it returns a value (which is dependent on the value
> generated by RAND because the std. dev and mean are constant). But say I
> want to limit the value returned by NORMINV to be within a certain range
(a
> min of 15 and a max of 60). Is there any way of setting the spreadsheet
up
> so that it recalculates RAND until the value returned by NORMINV is
within
> that set range of values?

Without truncation, you can use =NORMINV(RAND(),Mean,StDev), which
associates uniform random numbers [0,1) with normal cumulative
probabilities.

With truncation, you can associate the uniform random number with a normal
cumulative probability in the range between the cumulative probability of
the minimum value and the cumulative probability of the maximum value.

Using defined names, the new cumulative probability is:

=NORMDIST(MinX,Mean,StDev,TRUE)+RAND()*(NORMDIST(MaxX,Mean,StDev,TRUE)-NORMD
IST(MinX,Mean,StDev,TRUE)

Using this restricted range of cumulative probabilities in the NormInv
function, you can have a single formula that meets your requirement:

=NORMINV(NORMDIST(MinX,Mean,StDev,TRUE)+RAND()*(NORMDIST(MaxX,Mean,StDev,TRU
E)-NORMDIST(MinX,Mean,StDev,TRUE)),Mean,StDev)

- Mike Middleton

[Dana De]

Hello. I am not an expert, so I will just offer some ideas.
Have you looked at the RANDBETWEEN function?

RANDBETWEEN(bottom,top)

As an another idea in Excel 97.
Have you looked at <Tools> <Data Analysis> <Random Number Generator> ?
There are a few statistical options here.

I do not think Solver will work because it is not a continuous function.
Perhaps write a macro to keep plugging in random values until you get what
you need.
Just some ideas.

Garry Doyle wrote in message
<01bd2c65$566f5da0$4502...@doyle.interlog.com>...

>I need some help. Is there any way that I can truncate the values
>generated by the NORMINV function so that they are limited to a specific
>range?
>
>For example, if I have a normal distribution with a std. dev of 5 and a
>mean of 35 and I generate a probability using RAND() and I plug them into
>the NORMINV function it returns a value (which is dependent on the value
>generated by RAND because the std. dev and mean are constant). But say I
>want to limit the value returned by NORMINV to be within a certain range (a
>min of 15 and a max of 60). Is there any way of setting the spreadsheet up
>so that it recalculates RAND until the value returned by NORMINV is within
>that set range of values?
>