2.12. Excel Tables and @RISK

@RISK and TopRank can handle Excel tables and data tables without any special action on your part.

If the table contains @RISK functions, it will get re-evaluated in every iteration, which can be time consuming.  Also, if you have @RISK functions in a data table, as @RISK rewrites formulas while setting up the simulation, the data table will get re-evaluated once for each @RISK function, and therefore the simulation will take longer to start.  (For why @RISK must do this, see @RISK Changes Worksheet Formulas.)

If your model's logic really does not need @RISK functions inside a data table, removing them may speed up your simulations.

If you have release 5.7.0 or earlier, you should know about an Excel behavior that looked like a problem in @RISK.  When you enter or edit a formula in a cell adjacent to a table, Excel may expand the table to include the additional row or column.  As mentioned above, when starting a simulation or analysis, @RISK and TopRank rewrite all formulas that include @RISK or TopRank functions.  That rewrite sometimes triggers Excel to expand the table.  This doesn't affect 5.7.1 and newer releases, but if you have an earlier release, either upgrade your software or structure your models with at least one blank row or column between your table and the rest of your model.

Pivot tables are not automatically recalculated in an @RISK simulation, and in fact you don't want to recalculate a pivot table if it doesn't depend on any @RISK functions.  If you have any pivot tables that do depend on @RISK functions, create an after-iteration macro that calls Excel's RefreshTable method for each pivot table, and register that macro on the Macros tab of @RISK's Simulation Settings. A very basic example is attached.

TopRank does not provide for executing macros within an analysis.  If you have pivot tables that depend on any of your TopRank inputs, the analysis may not be correct because those pivot tables are not recalculated.  If your pivot tables don't depend on your TopRank inputs, then the analysis will be performed correctly.