Functions for Use in Microsoft Excel Models

You can use subroutines and functions in the Crystal Ball Developer Kit to automate certain Crystal Ball operations. The following Crystal Ball functions are available as spreadsheet functions for use in Microsoft Excel models:

CB.GetAssumFN — Retrieves information for a specific assumption cell
CB.GetAssumPercentFN — Returns the value corresponding to a percentile for an assumption cell
CB.GetCertaintyFN — Returns the certainty level of achieving a forecast value at or below a specific threshold
CB.GetForeDataFN — Returns the value for the given trial for a specific forecast
CB.GetForePercentFN — Returns the value corresponding to a percentile for a specific forecast
CB.GetForeStatFN — Returns statistic for a specific forecast cell
CB.GetRunPrefsFN — Returns a Run Preference setting
CB.IterationsFN — Returns the number of trials run in a simulation

Note:

For descriptions of these functions, including their arguments, search for the name without “FN” in Chapter 3 of the Oracle Crystal Ball Developer's Guide. For example, to locate arguments for CB.GetAssumFN, locate the description for CB.GetAssum.

To use these functions with Crystal Ball loaded:

Select an empty spreadsheet cell and select Insert, and then Function. In Microsoft Excel 2007 or later, select Formulas, and then Insert Function.
Select the Crystal Ball category in the category list box.
Find the function in the function list. Select it and click OK. Always select the function ending in FN, since the other runs significantly slower in most cases.
In the Function Arguments dialog, enter any required arguments and click OK.

The requested value is displayed in the cell with the function. Be sure to save the workbook with the new function while Crystal Ball is open.

Note:

CB.GetForeStatFN only pulls N-1 trials during a recalculation for either a single step or a full simulation, but will reflect full trials at the end of the single step or full simulation since a final recalculation is always performed.