Knowledge Base


Accessing Raw Simulation Data

You can see the values generated by any cell in the spreadsheet at any iteration using the function SimulationValue. This function takes a reference cell (the target), and a trial index. The trial index is 1-based (meaning the first trial is trial 1, and so on).

You should not create large tables of SimulationValue functions, if at all possible, as this can slow down a simulation dramatically. Instead, use the SimulationValuesArray function.

SimulationValuesArray returns an Array. You can insert an Array into a range of cells, which is the same as creating a table but requires only one function call. To do that, select a range (1 column by X rows).
In the formula bar, enter the formula

=SimulationValuesArray(C2)

(where C2 is the cell we are interested in), and press Control+Shift+Return.


You can also use Arrays in most (but not all) functions that take ranges of values. For example, the following will work:

=AVERAGE(SimulationValuesArray(C2))

which will be the same as the result of

=SimulationMean(C2)

You can also use the functions SUM, COUNT, and may others. But you can’t use COUNTIF, which does not work with Arrays.

Instead of COUNTIF, you can construct functions using SUM and IF and some criteria. We got the idea for this from this Microsoft Article about counting values. For example, to see how many times the value was less than zero, use

=SUM(IF(SimulationValuesArray(C2) < 0, 1, 0))

remember to press Control+Shift+Return when entering this formula.


Search the Knowledge Base

Structured Data LLC

Structured Data LLC is a software services and consulting firm founded in 2005, with offices in New York and San Francisco.

More Information?

If you'd like more information, please enter your email address below and we will get in touch.

Contact Us