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 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
(where C2 is the cell we are interested in), and press
You can also use Arrays in most (but not all) functions that take ranges of values. For example, the following will work:
which will be the same as the result of
You can also use the functions
COUNT, and may others. But you can’t use
COUNTIF, which does not work with Arrays.
COUNTIF, you can construct functions using
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.