How to generate random X given only min, max, mean?

[[email protected]]

Suppose I know only -5 <= X <= 95 with Xmean = 75.
I believe that means that P(-5 <= X <= 75) = 20% and
P(75 <= X <= 95) = 80%, roughly. Right?

1. Suppose that I believe X is uniformly distributed in
each subrange. Then I could generate random X by:

X = if RAND() <= .20 then -5 + 80*RAND() else 75 + 20*RAND()

Is there a more elegant formulation? Perhaps a
closed-form expression, something of the form
(which is obviously wrong):

X = -5 + 80*RAND() + 20*RAND()

2. Suppose that I believe X is "nearly normally" distributed
across the range [-5,95], but with a left skew that pulls
the mean to the right.

How would I generate random X? Perhaps
something of the form (which is obviously wrong):

X = NORMINV(RAND(), 75, 12.5)

where 12.5 = (95-(-5))/8 is the approximate sd
(z = 4) if the mean were 45 ((-5+95)/2).

(There probably is not just one answer, since I said
nothing about the kurtosis. Frankly, I know nothing
about kurtosis. Assume the same kurtosis as a standard
normal curve or whatever other simplifying assumption
makes sense.)

[Jay Petrulis]

[email protected] wrote:
> Suppose I know only -5 <= X <= 95 with Xmean = 75.
> I believe that means that P(-5 <= X <= 75) = 20% and
> P(75 <= X <= 95) = 80%, roughly. Right?
>
> 1. Suppose that I believe X is uniformly distributed in
> each subrange. Then I could generate random X by:
>
> X = if RAND() <= .20 then -5 + 80*RAND() else 75 + 20*RAND()
>
> Is there a more elegant formulation? Perhaps a
> closed-form expression, something of the form
> (which is obviously wrong):
>
> X = -5 + 80*RAND() + 20*RAND()
>
> 2. Suppose that I believe X is "nearly normally" distributed
> across the range [-5,95], but with a left skew that pulls
> the mean to the right.
>
> How would I generate random X? Perhaps
> something of the form (which is obviously wrong):
>
> X = NORMINV(RAND(), 75, 12.5)
>
> where 12.5 = (95-(-5))/8 is the approximate sd
> (z = 4) if the mean were 45 ((-5+95)/2).
>
> (There probably is not just one answer, since I said
> nothing about the kurtosis. Frankly, I know nothing
> about kurtosis. Assume the same kurtosis as a standard
> normal curve or whatever other simplifying assumption
> makes sense.)

If you can use VBA, try the following UDFs. I am not sure if the
truncated normal curve is the mathematically correct way to do this,
but it may be worth a shot. Also, this does not incorporate any skew
or kurtosis coefficients.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function _
TruncNormalRandom _
( _
Optional mean As Double = 0, _
Optional SD As Double = 1, _
Optional LowLimit, _
Optional UpperLimit, _
Optional IsStatic As Boolean = False _
) As Double

If Not (IsStatic) Then
Excel.Application.Volatile
End If
Randomize

Dim x As Double, _
temp As Double

x = NormalRandom(mean, SD)
If IsMissing(LowLimit) And IsMissing(UpperLimit) Then
' do nothing
ElseIf IsMissing(LowLimit) Then
Do Until x <= UpperLimit
x = NormalRandom(mean, SD)
Loop
ElseIf IsMissing(UpperLimit) Then
Do Until x >= LowLimit
x = NormalRandom(mean, SD)
Loop
Else
If LowLimit > UpperLimit Then
temp = LowLimit
LowLimit = UpperLimit
UpperLimit = temp
End If
If LowLimit = UpperLimit Then
x = LowLimit
Else
Do Until x >= LowLimit And x <= UpperLimit
x = NormalRandom(mean, SD)
Loop
End If
End If

TruncNormalRandom = x
End Function

Function _
NormalRandom _
( _
Optional mean As Double = 0, _
Optional SD As Double = 1, _
Optional IsStatic As Boolean = False _
) As Double

If Not (IsStatic) Then
Excel.Application.Volatile
End If

Randomize
NormalRandom = Sqr(-2 * Log(Rnd)) * Cos(2 *
Excel.Application.Pi * Rnd)
'NormalRandom = Sqr(-2 * Log(Rnd)) * Sin(2 *
Excel.Application.pi * Rnd)

NormalRandom = NormalRandom * SD + mean
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

[Jay Petrulis]

Oops! Sorry about the VBA use question (forgot the group name, duh!).
Anyway, have a look at the following thread as well.

http://groups.google.com/group/micro...98ead09e8ccf72

[goober]

=RAND()*(95+5)-5

This will generate a random number between -5 and 95 using formulas in a cell of the worksheet.

[nomail1983]

"goober" wrote:
> =RAND()*(95+5)-5
> This will generate a random number between
> -5 and 95 using formulas in a cell of the worksheet.

I believe that results in a uniform distribution over [-5,95]
with a mean of 45. I am interested in a distribution with
a mean of 75, P(-5 <= X <= 75) = 20% and
P(75 <= X <= 95) = 80%. A uniform distribution over
[-5,95] would effectively yield just the opposite, I believe.