xllmonte

Functions and macros of the xllmonte add-in.

Usage

This add-in performs Monte Carlo simulation in Excel. Instead of creating many cell with random values and using the built-in Excel functions to aggregate those, Monte can use one cell that produces random values and aggregate those over the course of a simulation. Monte trades space for time.

Action Key  Description
Run Ctrl-Shift-R  Start a simulation. Press the Escape key to stop.
stop Esc  Terminate a simulation.
Step Ctrl-Shift-S  Single step through a simulation.
reseT Ctrl-Shift-T  Prepare a new simulation.

Over the course of a simulation you can collect statistics such as maximum, minimum, mean, and standard deviation. Monte does not store any simulation results so it is possible to run as many iterations as you wish.

The Excel message bar on the bottom left displays the number of iterations, the elapsed time, and the iterations per second. The functions MONTE.COUNT and MONTE.ELAPSED can be used to display this information in the spreadsheet.

Monte adds the time dimension to your spreadsheet. If you enter =MONTE.MEAN(RAND()) in a cell and start a simulation then every time RAND() is recalculated the cell will be updated with the running average of the calls to RAND().

Category Monte

MONTE.AVERAGE Calculate the average of cell over a simulation.
MONTE.BUFFER Buffer rows to ouput range from the top.
MONTE.CALCULATION Get or set calculation mode. Default is MONTE_CALCULATE_DOCUMENT.
MONTE.CDF Return the probabilities cell is less than or equal to values.
MONTE.CONDITIONAL Return range when condition is true, otherwise do nothing.
MONTE.COUNT Return current iteration count starting from 1.
MONTE.ELAPSED Return elapsed time in seconds since the start of the simulation.
MONTE.MAX Return the maximum values of a cell over a simulation.
MONTE.MEAN Calculate the mean of a cell over a simulation.
MONTE.MIN Store the minimum values of a cell over a simulation.
MONTE.PAUSE Pause simulation if condition is true.
MONTE.RECOUNT Return a two cell array of condition and current recount.
MONTE.RESET Prepare for a new simulation using Ctrl-Shift-T.
MONTE.RUN Run a simulation using Ctrl-Shift-R.
MONTE.RUNNING Return TRUE if the simulaton is running.
MONTE.STDEV Calculate the mean and standard deviation of a cell over a simulation.
MONTE.STEP Single step a simulation using Ctrl-Shift-S.
MONTE.STOP Stop the simulation if condition is true.
MONTE.SUM Calculate the running sum of a cell over a simulation.
MONTE.TABLE Return rows of a table during a simulation.
MONTE.UPDATE Set the update interval for the simulation.
MONTE.WHEN When condition is true return range, otherwise do nothing.
MONTE.ZIG Return first times in sequential order.
RANDOM.BROWNIAN Generate Brownian samples at given times.
RANDOM.NORMAL Generate a normal random variate having given mean and stdev.
RANDOM.SEED Use array of numbers to seed a random number generator.
RANDOM.UNIFORM Generate random variate uniformly in the interval [a, b).
\RANDOM.GENERATOR Return a handle to a random number generator.

Category XLL

COLLECT Collect cells into a range
DEPENDS Return cell after dependent is calculated.
THIS Return the contents of the calling cell.