Implementing Random Number Generators with Specified Distributions and Distribution Parameters in Excel
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.
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.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
d-glitch: I guess I could use a table lookup - haven't done that in the simulations so far.
I don't really have a good idea or what you are doing.
This is my understanding:
How many variables are there and how many data points per variable?
How good, how real is your data?
What sort of accuracy/resolution do you need or want?
Just curious, but are you doing financial modeling or something else?
Options for custom RNG's are analytical functions, fitted polynomials, piece-wise linear functions, and lookup tables.
The first two would work the best with Excel, but may be the hardest do derive.
The last two are easier to derive but harder to handle in Excel.
This is my understanding:
- You have some real data for several input variables.
- You analyze this data and fit it to a distribution.
- You want to make an RNG to simulate particular variables. You may need several different RNG's.
- Finally you want to run Monte Carlo simulations using your RNG's.
How many variables are there and how many data points per variable?
How good, how real is your data?
What sort of accuracy/resolution do you need or want?
Just curious, but are you doing financial modeling or something else?
Options for custom RNG's are analytical functions, fitted polynomials, piece-wise linear functions, and lookup tables.
The first two would work the best with Excel, but may be the hardest do derive.
The last two are easier to derive but harder to handle in Excel.
ASKER
There are about a dozen variables which I'm treating as independent - although only because that's simpler.
I've tested correlation and didn't find much measure of interdependency so it's likely justified.
I'm only using what I'd call "internal" variables and no global trends, etc.
There are around 100 data points of real data per variable.
I don't need much accuracy or resolution.
This is financial modeling in this case.
I've tested correlation and didn't find much measure of interdependency so it's likely justified.
I'm only using what I'd call "internal" variables and no global trends, etc.
There are around 100 data points of real data per variable.
I don't need much accuracy or resolution.
This is financial modeling in this case.
It is good that you are testing correlation. I was wondering about that.
If there are no cross-correlations, that should mean than none of the variables are correlated with time either. Is that true, and does it make sense? Is your subsystem stable and isolated from seasonal trends and global market forces?
If there are no cross-correlations, that should mean than none of the variables are correlated with time either. Is that true, and does it make sense? Is your subsystem stable and isolated from seasonal trends and global market forces?
ASKER
d-glitch: Of course, time either...I didn't see any temporal correlation of note.
The system is likely NOT isolated from seasonal trends and global and local market forces. But the behavior doesn't seem to indicate much of that.
The historical data is 10 years in length taken monthly. That's where I get the distributions. Then, it seems reasonable to weigh the predictions with more recent data.
The objective is to project 12 months into the future.
The outcome is the sum of the variables and I'm more interested in projecting the 10% probable low value than anything - although the expected value is of some interest as well.
The system is likely NOT isolated from seasonal trends and global and local market forces. But the behavior doesn't seem to indicate much of that.
The historical data is 10 years in length taken monthly. That's where I get the distributions. Then, it seems reasonable to weigh the predictions with more recent data.
The objective is to project 12 months into the future.
The outcome is the sum of the variables and I'm more interested in projecting the 10% probable low value than anything - although the expected value is of some interest as well.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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.