Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel probability distributions
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel probability distributions
I meant to say 2*stdev ~= 300.
"rcc" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel probability distributions
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel probability distributions
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? "Mike Middleton" wrote: 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel probability distributions
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" 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? "Mike Middleton" wrote: 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel probability distributions
On Sep 27, 11:34 am, rcc wrote:
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel probability distributions
Thanks, Jim.
"Jim Cone" wrote: 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" 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? "Mike Middleton" wrote: 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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel probability distributions
Hello,
I suggest to use my UDF rand_trigen: http://www.sulprobil.com/html/distributions.html Regards, Bernd |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel probability distributions
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 - XX -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "rcc" wrote in message Thanks, Jim. "Jim Cone" wrote: 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CHANGING THE DISTRIBUTIONS OF THE INFORMATION | Excel Worksheet Functions | |||
Excel should be able to plot better probability graphs | Charts and Charting in Excel | |||
frequency distributions | Excel Discussion (Misc queries) | |||
Using RAND() with Different Distributions!? | Excel Worksheet Functions | |||
How do I graph normal probability distributions in excel? | Excel Worksheet Functions |