NORM.INV(RAND(), mean, standard deviation) for enough iterations, say 20 000 rows, the combined results if plotted will mimic a normal distribution. This will have a maximum and minimum value dictated by the mean and the standard deviation chosen.įig. NORM.INV(probability, mean, standard deviation) is a function which backs out the value of the normal cumulative distribution function for a given probability between 0 and 1, a given mean value, and a given standard deviation. RAND() is a simple command which produces a random number evenly distributed between 0 to 1, but note it resets on every press of return or on a save. There are two key functions which when used together can be used in Excel to perform a Monte Carlo simulation. I give a link to some example spreadsheets at the end of the article, so feel free to download these, but use at your own risk. I’m not aware of any gremlins, but you never know. For what it’s worth, while the spreadsheets are geared towards oil and gas prospect volumetrics, the excel workflow described isn’t limited to that, it can be applied to anything with multiple variables requiring Monte Carlo simulation, if you have the imagination and the time required to throw a few spreadsheets together. We all have a bit of a love-hate relationship with excel I guess, but after a sneaking suspicion that the program had to be clever enough to tackle it, turns out it is. The route is a little bit cumbersome, but if you ever find yourself needing to do some probabilistic volumetrics and lacking resource to buy more expensive software, here’s a leg-up. Perhaps there are more streamlined ways of doing it and linked-in’s bottomless pool of excel experts might chime in with improvements, but as it stands it seems to get from A to B. Excel’s ability to do this may be old news to many of you, but I was never told about its capabilities in this regard, so I thought I’d share them more widely. However, they do cost a bit, and for some chapters of life we have more time than we do cash. At times like that, it’s always worth pondering what you can do in your existing software kits if you take them to the max. These days I tend to use Crystal Ball for generic volumetrics, but before I bit that particular bullet, I had always wondered whether it might be possible to perform Monte Carlo simulations in excel. There are a lot of fantastic bits of kit out there for performing Monte Carlo simulations, and they are well worth the investment if you do a lot of that stuff, as they give greater flexibility, empower greater complexity, and save a lot of time. Where there’s a will The dynamic duo Normalising then applying the range Uniform and lognormal Calculating statistics for the results Plotting to check Obtaining variable products Linking to a single inputs page and doing a deterministic check Adding the outputs Rolling the dice a few more times The min-max caveat – be very careful Wider application Some example templates 1: A summary of the key excel functions and the workflow
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |