RESEARCH ARTICLES | RISK + CRYSTAL BALL + ANALYTICS

 
Search

Oracle Crystal Ball Spreadsheet Functions For Use in Microsoft Excel Models

Author: Eric Torkia, MASc/Tuesday, May 20, 2014/Categories: Monte-Carlo Modeling

Rate this article:
2.3
Oracle Crystal Ball Spreadsheet Functions For Use in Microsoft Excel Models

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

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

 

Print

Number of views (9221)/Comments (0)

Tags: Advanced ModelingCrystal BallExcelExcel VBA
Please login or register to post comments.