Excel probability distributions

[rcc]

My apologies if this is more an "academic" question than an Excel question...

I'm looking to create a probability distribution in Excel. My probability
distribution is similar to a normal distribution, but it's scewed. My data
ranges (for example) from 200 to 800 and has a mean of 400. If my mean were
500, then this would look like a normal curve with 2*stdev of approx 150.
But since the mean is 400, the curve is scewed to the left a bit.

Does anyone have an idea as to what kind of probability distribution I would
use for this? My end result is using the distribution for a Monte Carlo
simulation (which I have all set up in a worksheet).

Thank you.

 

[rcc]

I meant to say 2*stdev ~= 300.

 

[Mike Middleton]

rcc -

Instead of using a distribution defined by a mathematical function, you
could use a piecewise-linear approximation of the cumulative distribution.
Since you can use as many points as you want to describe the cumulative
distribution, you can get a very close fit.

RiskSim, my Monte Carlo simulation add-in for Excel, has a RandCumulative
function. Other simulation add-ins have similar features.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[rcc]

Thanks, Mike. I'm not sure if that would work in my case since I don't
actually have a data set to create a cumulative distribution from in the
first place. Really the only information I have is the upper and lower
bounds and the mean. It's from those values that I want to create a
distribution that looks similar to a normal distribution.

One idea that I had is to sort of combine two normal curves together. I
would have a piecewise function that described a normal distribution. On one
side of the mean, the function would be described by one standard deviation
and on the other side of the mean, another standard deviation. Is that too
much a hack to be theoretically sound?

 

[Jim Cone]

Specify the parameters: low, high, average and set size.
Set size is limited to the number of cells in a row, however, with
minor modifications the set size could be increased to the number
of cells in a column.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


Sub MakeNumbers()
Call FindNumbersThatAverage(dblLow:=200, dblHigh:=800, dblTarget:=400, lngSetSize:=100)
End Sub

Function FindNumbersThatAverage(ByRef dblLow As Double, ByRef dblHigh As Double, _
ByRef dblTarget As Double, ByRef lngSetSize As Long)
' Provides random numbers that average a predetermined amount.
' May 29, 2005 - Jim Cone - San Francisco, USA
' Sept 27, 2007 - Radically revised by Jim Cone.
Dim j As Long
Dim lngN As Long
Dim lngTemp As Long
Dim lngArray() As Long
Dim dblAdjust As Double
ReDim lngArray(1 To lngSetSize)

'Sanity check
If dblLow > dblTarget Or dblHigh < dblTarget Then Exit Function
'If target value is not centered between high and low values then this
'allocates the random values proportionally.
dblAdjust = (dblHigh - dblTarget) / (dblHigh - dblLow)

Randomize
For lngN = 1 To (dblAdjust * lngSetSize)
'Get random values between the target and low parameter.
lngTemp = Int(Rnd * (dblTarget - dblLow) + dblLow)
'Place random values in the array
lngArray(lngN) = lngTemp
Next 'lngN

Randomize
For lngN = (dblAdjust * lngSetSize + 1) To lngSetSize
'Get random values between the target and high parameter.
lngTemp = Int(Rnd * (dblHigh - dblTarget) + dblTarget)
'Place random values in the array
lngArray(lngN) = lngTemp
Next 'lngN

'Do a random sort on the array values.
Randomize
For lngN = lngSetSize To 1 Step -1
j = Int(Rnd * lngN + 1)
lngTemp = lngArray(lngN)
lngArray(lngN) = lngArray(j)
lngArray(j) = lngTemp
Next 'lngN

'Stick it on the worksheet (in a single row).
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, lngSetSize).Value = lngArray()
End Function
'------------------



"rcc"
<[email protected]>
wrote in message
Thanks, Mike. I'm not sure if that would work in my case since I don't
actually have a data set to create a cumulative distribution from in the
first place. Really the only information I have is the upper and lower
bounds and the mean. It's from those values that I want to create a
distribution that looks similar to a normal distribution.

One idea that I had is to sort of combine two normal curves together. I
would have a piecewise function that described a normal distribution. On one
side of the mean, the function would be described by one standard deviation
and on the other side of the mean, another standard deviation. Is that too
much a hack to be theoretically sound?

 

[joeu2004]

I'm looking to create a probability distribution in Excel. My probability
distribution is similar to a normal distribution, but it's scewed. My data
ranges (for example) from 200 to 800 and has a mean of 400. If my mean
were 500, then this would look like a normal curve with 2*stdev of approx
150. But since the mean is 400, the curve is scewed to the left a bit.

Does anyone have an idea as to what kind of probability distribution I
would use for this?

Have you looked at the beta distribution?

I had a similar problem some time ago. I tried to make the beta
distribution work for me. I don't believe I succeeded. But it might
make more sense to you.

 

[rcc]

Thanks, Jim.

Specify the parameters: low, high, average and set size.
Set size is limited to the number of cells in a row, however, with
minor modifications the set size could be increased to the number
of cells in a column.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


Sub MakeNumbers()
Call FindNumbersThatAverage(dblLow:=200, dblHigh:=800, dblTarget:=400, lngSetSize:=100)
End Sub

Function FindNumbersThatAverage(ByRef dblLow As Double, ByRef dblHigh As Double, _
ByRef dblTarget As Double, ByRef lngSetSize As Long)
' Provides random numbers that average a predetermined amount.
' May 29, 2005 - Jim Cone - San Francisco, USA
' Sept 27, 2007 - Radically revised by Jim Cone.
Dim j As Long
Dim lngN As Long
Dim lngTemp As Long
Dim lngArray() As Long
Dim dblAdjust As Double
ReDim lngArray(1 To lngSetSize)

'Sanity check
If dblLow > dblTarget Or dblHigh < dblTarget Then Exit Function
'If target value is not centered between high and low values then this
'allocates the random values proportionally.
dblAdjust = (dblHigh - dblTarget) / (dblHigh - dblLow)

Randomize
For lngN = 1 To (dblAdjust * lngSetSize)
'Get random values between the target and low parameter.
lngTemp = Int(Rnd * (dblTarget - dblLow) + dblLow)
'Place random values in the array
lngArray(lngN) = lngTemp
Next 'lngN

Randomize
For lngN = (dblAdjust * lngSetSize + 1) To lngSetSize
'Get random values between the target and high parameter.
lngTemp = Int(Rnd * (dblHigh - dblTarget) + dblTarget)
'Place random values in the array
lngArray(lngN) = lngTemp
Next 'lngN

'Do a random sort on the array values.
Randomize
For lngN = lngSetSize To 1 Step -1
j = Int(Rnd * lngN + 1)
lngTemp = lngArray(lngN)
lngArray(lngN) = lngArray(j)
lngArray(j) = lngTemp
Next 'lngN

'Stick it on the worksheet (in a single row).
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, lngSetSize).Value = lngArray()
End Function
'------------------



"rcc"
<[email protected]>
wrote in message
Thanks, Mike. I'm not sure if that would work in my case since I don't
actually have a data set to create a cumulative distribution from in the
first place. Really the only information I have is the upper and lower
bounds and the mean. It's from those values that I want to create a
distribution that looks similar to a normal distribution.

One idea that I had is to sort of combine two normal curves together. I
would have a piecewise function that described a normal distribution. On one
side of the mean, the function would be described by one standard deviation
and on the other side of the mean, another standard deviation. Is that too
much a hack to be theoretically sound?

 

[Bernd P]

Hello,

I suggest to use my UDF rand_trigen:
http://www.sulprobil.com/html/distributions.html

Regards,
Bernd

 

[Jim Cone]

Something more ...
I have revised the code I posted earlier.
It now can generate up to 6,500,000 numbers with the specified average
and the upper and lower constraints.
(3 million numbers run in about 8 seconds using xl2002 on my machine)

It comes in a workbook...click the button, fill in the options and off you go.
If you or others are interested in a free copy, you can email me direct and
request it. I respond best to those with real names and a location.
Remove xxx from my email address - (e-mail address removed)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"rcc"
wrote in message
Thanks, Jim.

Specify the parameters: low, high, average and set size.
Set size is limited to the number of cells in a row, however, with
minor modifications the set size could be increased to the number
of cells in a column.

-snip-