I'm working with Excel workbooks and running Monte Carlo simulations which require random number generators. Each random number generator is used to represent real data for which we have history - and so, can extract a likely distribution and its parameters using Analytic Solver.
With that, we can (conceptually) create an Excel formula to generate random numbers with the same distribution.
I find that matching formulations for such RNGs is elusive at times.
I was able to Google and find a formula for the Weibull distribution and am able to extract the needed parameters with an analysis program run on the historical data. Normal distribution too.
But, this isn't always the case.
There are really two questions here:
1) What is a formula that I can use in Excel to generate random numbers with a Pearson 5 distribution?
2) Where might one look to find Excel formulas for generating random numbers with each of a variety of distributions?
3) Or is there some transformation technique that generalizes this requirement leading to Excel formulas (I rather have to doubt it).
I want to do this in Excel without commercial add-ins because I have users with production Excel workbooks.
ASKER
Right now, I'm parametrizing the Weibull distribution in Excel like this:
Open in new window
So, that's pretty handy as long as I can get the parameters Scale Factor and Shape Factor - which I've been able to do.But now I've found a distribution in historical data that matches Pearson 5. I can extract parameters for that using Analytic Solver but I don't have an expression to plug them into. No Pearson 5 at all.
What you have mentioned: And, lacking a closed form, suggests that one could generate a curve for the PDF and do some sort of approximation to it - but that would be relatively brute force, no? And, selecting the form of the approximant expression, would be an adventure. But maybe if all else fails?
Right now I'm more interested in finding a canned solution rather than developing something all new.