Download Crystal Ball, @RISK, ModelRisk and ProModel Software, Example Models and White-Papers

telHave a question? Give us a call at 1-888-879-8440
Monday to Friday (9am-9pm EST)

Search all articles


Buy Software and Services for Oracle Crystal Ball, Palisade DecisionTools Suite, ProModel, Frontline Solver)
Login for analytics downloads and resources

Free consultation with a Technology Partnerz consultant on training, consulting and software questions
Learn about remote support services

See us on yourtubeFollow Technology Partnerz on Twitter


The Consultant's Corner - Insight into analytics and other cool stuff...

May 20

Written by: Eric Torkia
5/20/2014 12:33 PM  RssIcon

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:

  1. Select an empty spreadsheet cell and select Insert, and then Function. In Microsoft Excel 2007 or later, select Formulas, and then Insert Function.

  2. Select the Crystal Ball category in the category list box.

  3. 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.

  4. In the Function Arguments dialog, enter any required arguments and click OK.

Things to remember

  1. 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
  2. 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.
  3. If you forget to load CB, your CB.Functions will produce the #NAME error
  4. Be sure to save the workbook with the new function while Crystal Ball is open.

Crystal Ball Functions For Use in Microsoft Excel Models




Retrieves information for a specific assumption cell.


Returns the value corresponding to a percentile for an assumption cell.


Calculates the specified statistic for the assumption in the specified cell.


Returns the certainty level of achieving a forecast value at or smaller than a specific threshold.


Returns the value for the given trial for a specific forecast cell.


Returns the value corresponding to a percentile for a specific forecast


Returns statistic for a specific forecast cell.


Returns a Run Preference setting.


Returns the number of trials run in a simulation.


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


Copyright ©2014 Eric Torkia