Avatar of hypercube
hypercubeFlag for United States of America

asked on 

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.
Statistical PackagesMath / ScienceMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
hypercube
ASKER CERTIFIED SOLUTION
Avatar of d-glitch
d-glitch
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of hypercube
hypercube
Flag of United States of America image

ASKER

d-glitch:  Thank you!
Right now, I'm parametrizing the Weibull distribution in Excel like this:

[Scale Factor]*(-LN(1-RAND()))^(1/[Shape Factor])

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:
 find a closed form integral of the PDF.
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.
Avatar of hypercube
hypercube
Flag of United States of America image

ASKER

d-glitch:  I guess I could use a table lookup - haven't done that in the simulations so far.

Avatar of d-glitch
d-glitch
Flag of United States of America image

I don't really have a good idea or what you are doing.
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.
Avatar of hypercube
hypercube
Flag of United States of America image

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.


Avatar of d-glitch
d-glitch
Flag of United States of America image

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?
Avatar of hypercube
hypercube
Flag of United States of America image

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.
Microsoft Excel
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo