Oracle Crystal Ball Spreadsheet Functions For Use in Microsoft Excel Models The Cutting Edge Eric Torkia Advanced ModelingCrystal BallExcelExcel VBA May 20 2014 Oracle Crystal Ball has a complete set of functions that allows a modeler to extract information from both inputs (assumptions) and outputs (forecast). Used the right way, these special Crystal Ball functions can enable a whole new level of analytics that can feed other models (or subcomponents of the major model). Understanding these is a must for anybody who is looking to use the developer kit. The table below lists Crystal Ball functions that are also available as spreadsheet functions for use in Microsoft Excel spreadsheet models. 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. Things to remember The requested value is displayed in the cell with the function only after running either a trial or a full simulation. Otherwise you will get a #NUM error 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. If you forget to load CB, your CB.Functions will produce the #NAME error Be sure to save the workbook with the new function while Crystal Ball is open. Crystal Ball Functions For Use in Microsoft Excel Models Name Description CB.GetAssumFN Retrieves information for a specific assumption cell. CB.GetAssumPercentFN Returns the value corresponding to a percentile for an assumption cell. CB.GetAssumStatFN Calculates the specified statistic for the assumption in the specified cell. CB.GetCertaintyFN Returns the certainty level of achieving a forecast value at or smaller than a specific threshold. CB.GetForeDataFN Returns the value for the given trial for a specific forecast cell. 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. CB.Spearman Calculates Spearman rank correlations between pairs of values. Index Parameters To see a complete list of index parameters compatible with all the CB Functions, click here