Knowledge Base

What Were the Input Values Leading to...

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: SimulationValue and 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.

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