If you see an unexpected result – something very large or very small – you may want to see what the input values were leading to that result. Or, you might want to see the input values leading to a mean result.
In either case, there are two functions you need:
SortedSimulationIndex. This may seem confusing at first, but if you work through an example it should make sense.
SimulationValue returns the value of a given cell at a particular trial. This is 1-based (the first trial is trial 1). So if you want to see the input value from cell A1 in the first trial, that would be
=SimulationValue( A1, 1 )
So far so good. Now suppose your result value is in cell B2, and you want to see what value at A1 led to the low value at B2.
SortedSimulationIndex will order the results of the simulation by the values in a particular cell (in ascending order – lowest first). The result of this function is a trial number. That trial number can be plugged into the
SimulationValue function to find the value of another cell.
It works this way so you can choose to sort by any cell in the spreadsheet, then find the value of another cell. So to find what value in A1 caused the low value in B2,
=SimulationValue( A1, SortedSimulationIndex( B2, 1 ))
Tip: to find the highest value, you need to use the highest trial number. The function
SimulationTrials returns the number of trials from the last simulation. Using this function, instead of a number, means you will always have the last trial, even if you change the number of iterations.