DiscoverSim Engine and Excel Formula Interpreter

Back to Table of Contents

DiscoverSim uses Excel for the user interface but random number generation, distribution fitting parameter estimation, accelerated simulation and optimization are performed using the Gauss Mathematical and Statistical Engine by Aptech (www.aptech.com). GAUSS is a fast matrix programming language widely used by econometricians and financial analysts since 1984. Communication between Excel and the GAUSS Engine is performed with Mercury by Econotron Software (www.econotron.com). Econotron Software has developed interprocess communication tools for a number of applications based on the Gauss Engine.

Accelerated mode simulation and optimization use DiscoverSims Excel Formula Interpreter. This is based on a Common Object Interface (COI) by Econotron Software to implement an Excel spreadsheet in GAUSS. The entire Excel workbook is represented as a GAUSS procedure using COI, with speed increases up to 40 times.

The interpreter supports the majority of all Excel numerical functions including those in the Analysis Toolpak, but currently does not support the following:

Excel 2007 CUBE functions
Excel 2010 AGGREGATE, NETWORKDAYS.INTL, WORKDAYS.INTL
CONVERT, GETPIVOTDATA, HYPERLINK
ISBLANK, ISTEXT, ISNONTEXT, ISLOGICAL, ISREF
PowerPivot Data Analysis Expressions (DAX) Functions such as ISO.CEILING
Third party vba functions

Functions supported but with limitations:

Excel functions that return an array or non-single value (e.g. LINEST, LOGEST, MINVERSE, MDETERM, MMULT, STANDARDIZE, TRANSPOSE and TREND) are limited to intermediate calculations. The final output result must be a scalar.
Excel functions that return a string value are limited to intermediate calculations. The final result must be a scalar.

If the DiscoverSim interpreter sees a function that it does not support, you will be prompted to use Excels Native mode, where the transfer function, Y = f(X), calculations are performed in Excel. Note that in this native mode, DiscoverSims random input distributions are still generated with GAUSS.

The Excel Formula Interpreter can be validated using Accelerated Mode - Run Validation using Native Excel. This runs a validation test to compare Accelerated Mode versus Native Excel. Each output is assessed by comparing the simulation means. If the worst case relative difference is less than or equal to 1e-10%, the test passes and the status is Success. If the relative difference is between 1e-10% to 1e-4%, the status is Good, and if greater than or equal to 1e-4%, the test status is Poor.

DiscoverSim can quickly perform millions of simulation replications, but is limited by system memory. If the user chooses to store the simulation data in Excel, then Excels row and column limitations will also apply (Excel 2007, 2010: 1,048,576 rows and 16,384 columns).

Engine and Excel Formula