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.)

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

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

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

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


--
goober
------------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=496073

"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.