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.