ExcelBanter - View Single Post - How to generate random X given only min, max, mean?

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
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''