Contact information:

#1: Simulation using Excel: Tricks, Trials, Trends

Excel is a general purpose spreadsheet which is widely used & understood, but rarely used by itself for simulations. However, the Data Table function in MS Excel can be used to execute substantial simulations, without requiring cumbersome programming "tricks" or VBA coding.  The result is an arbitrarily large results table in which each row is one iteration of the simulation, and each column is a random variable generated in the simulation.

A small number of additional probability functions are easily programmed using VBA to make Excel a general purpose simulation package.  Because VBA is interpreted, use of VBA functions can greatly limit the speed of a simulation.  However, for simulations of
small size and complexity, the ease and familiarity of working in Excel, outweigh the disadvantages of speed.  Examples from clinical trials will be used.
 
Finally, I discuss new methods to move simulations out of the black boxes and into the enterprise, based on work by Sam Savage.  Simulation results (a “SIP”, or “Stochastic Information Packet”) from multiple platforms can be stored as XML strings(using the DIST standard) in a “SLURP” (“Stochastic Library Unit with Relationships Preserved”), and from there used for reports, planning, etc, or incorporated into other simulations. 


The attached presentation is an introduction to doing simulation in MS Excel, using the Data Table function, covering:
* The basic method;
* Using inverse probability functions for validation, verification, and insight
* Some basic macros for setting up
* VBA code for some missing probability functions
* Examples (from clinical trials)
* Advantages, Disadvantages, and Limitations
* Additional Resources

Attached is:
(1) A power point tutorial on implementing basic simulations in Excel
(2) A presentation introducing the concept of Probability Management
(3) A presentation with some notes about non-introductory topics, such as validation, verification and sensitivity. (4) An Excel spreadsheet template containing macros which ease writing simulations, some missing probability functions written in VBA, and examples.
(5) Another Excel spreadsheet that contains a sampler of simulation functions, with a plot overlaying all random variables.
Č
Ċ
Dennis Sweitzer,
Jan 31, 2013, 10:47 AM
Ċ
Dennis Sweitzer,
Jan 31, 2013, 10:47 AM
Ċ
Dennis Sweitzer,
Jan 31, 2013, 10:47 AM
Ĉ
Dennis Sweitzer,
Oct 21, 2011, 5:00 PM
Ĉ
Dennis Sweitzer,
Oct 21, 2011, 5:01 PM
Comments