3.36. RiskSimtable with Distributions as Arguments

Applies to: @RISK, all releases

The @RISK manual says that RiskSimtable( ) can take distributions as arguments, but I can't get the syntax right. How should I code my RiskSimtable( ) function?

RiskSimtable( ) actually has one argument. It's an array, either a list of values in curly braces like {14,33,68,99} or an Excel range reference without curly braces like C88:C91. To use distribution functions as arguments to RiskSimtable( ), put them in a range of cells in Excel and then specify the range as the argument to RiskSimtable( ).

Please download the attached example, KB55_SimtableArguments.xlsx. It shows two methods to use RiskSimtable( ) to modify distributions from one simulation to the next. In each case, cell references are the key to making the behavior vary. In the example, Simulation Settings » Sampling specifies that multiple simulations all use the same seed. Thus, any differences between simulations are completely due to the different distributions chosen.

Method 1: There is only one distribution function, RiskBinomial( ), in this example. Its second argument, p, is a cell reference to a RiskSimtable( ) function that lists the value of p for each simulation. Other formulas would use the value of the distribution function, not the RiskSimtable( ) function.

Method 2: Each simulation uses a different distribution function. Those functions are defined in an array of cells, and the RiskSimtable( ) function has that array reference as its argument. Other formulas would use the value of the RiskSimtable( ) function, not the individual distribution functions.

There's one potential problem with that second method. Since the RiskSimtable( ) function refers to the three cells containing the three distribution functions, all three of them are precedents of the RiskSimtable. If one of your @RISK outputs refers to that RiskSimtable(), directly or indirectly, all three of the distributions will show as precedents of the output. Logically, in each of the three simulations, a different one of the functions is a precedent of your output. But since the RiskSimtable( ) function argument refers to all three, all three show up as precedents in each of the three simulations.

The solution is to wrap the RiskSimtable( ) inside a RiskMakeInput( ), as was done in the last block in the example. Then @RISK will not consider the precedents of the RiskSimtable( ) as precedents of the output, and the tornado diagram for the output will show just one bar in each of the three simulations, which makes sense logically.