To learn more about EpiX Analytics' work, please visit our modeling applications, white papers, and training schedule.

Page tree

The Custom Distribution

With the Custom Distribution in Crystal Ball you can represent the following six distributions.

1. Discrete Uniform

2. Discrete

3. General

4. Histogram

5. Cumulative ascending

6. A combination of the Discrete Uniform, the Discrete and/or the Histogram distribution into one distribution.

The first two of these six distributions are discrete, the next three are continuous and the last distribution is a combination of two or three discrete and/or continuous distributions.

The "Load Data" & "Keep linked to spreadsheet" options

When you use the Custom Distribution, we assume (and we highly recommend!) that you use the "Load Data" button and the "Keep linked to spreadsheet" option as described in more detail here. The "Load Data" button allows you to load data from your spreadsheet into the Custom Distribution, while the "Load Data" button links your data in your spreadsheet to the Custom Distribution. This allows "dynamic referencing" of the Custom Distribution as described here, which is one of the great improvements of Crystal Ball 7.0.

Depending on the format of the data you use, the Custom Distribution will represent one of six distributions. Data entry rules for all six distributions are presented below.

Option 1: A Discrete Uniform distribution

The Discrete Uniform distribution randomly picks any one of the values you give it with equal probability (sampling with replacement). There are three methods to format your data so that the Custom Distribution "knows" that it has to behave like a Discrete Uniform distribution as is shown below.

Method 1.

The first method is to have all the data in one column, from which the Custom Distribution then randomly picks one value at a time. The data in the figure below is in the column D11:D14, which resulted in the Discrete distribution shown below.

Method 2.

The second method is to provide the data in six or more columns, in the figure below we used F11:K14. The Custom distribution will then generate a Uniform Discrete Distribution as is shown in the figure below.

Method 3.

The third method is more complicated, but is more efficient for setting up a Uniform Discrete distribution with equal distances between the numbers. You have to use exactly four cells, as shown in the figure below. The first cell is the first value of the distribution, and the second cell the last value. The third cell can have any value as long as it is not an empty cell (in our example a "1" is used) and the fourth cell gives the step-size between the different data points as shown below.

Model Crystal Ball Custom Distribution (sheet Discrete Uniform) provides examples of all three methods.

Option 2: A Discrete distribution

The discrete distribution is a general type of function used to describe a variable that can take one of several explicit discrete values {xi} and where a probability weight {pi} is assigned to each value. There are two methods to modeling a Discrete distribution with Crystal Ball's Custom Distribution, as described below:

Method 1

The first and most flexible way is to provide the Custom Distribution with two columns; the left one with the values of your parameter and the right one with the probability weight of each value (C12:D18).

Method 2

In the second method, we provide the Custom Distribution with four columns as shown below; the first cell gives the first value of each bin (row), and the second cell the last value of each bin. The third cell is the probability of being in that specific bin and the fourth finally gives the steps between the different data points within the specific bin as shown below.

Model Crystal Ball Custom Distribution (sheet Discrete) provides an example of both methods to generate a Discrete Distribution.

Option 3: A General distribution

A General distribution is a continuous distribution and in Crystal Ball also called "sloping continuous ranges" distribution. To set up a General distribution with the Custom Distribution, Crystal Ball requires a 5-column format as is shown in  figure below. The five columns contain the following values:

  1. The first column has the minimum value of a range (but is left blank if the minimum is equal to the last range's maximum, see G11:G17)

  2. The second column has the maximum value of a range

  3. The third column has the relative probability height of the minimum (but is left blank if the height of the minimum is equal to the last range's maximum's height, see I11:I17)

  4. The fourth column is the relative probability height of the maximum

  5. The fifth column is left blank but is necessary to indicate that this is a General Distribution.

The spreadsheet model Crystal Ball Custom Distribution (sheet General) provides you with this example in Excel and used cell G10:K17 as input data.

Option 4: A Histogram distribution

To construct a Histogram distribution, you need to put your data into three neighboring columns:

1. the first has the minimum possible value of the range in that row,

2. the second the maximum possible value, and

3. the third column has the range frequencies (the use of absolute frequencies as in E13:E17 or relative frequencies as in I13:I17 result in exactly the same histogram distribution)

The figure below illustrate the construction of a Histogram distribution with the Custom Distribution. In the model Crystal Ball Custom Distribution (sheet Histogram) this example is also presented.

Option 5: A Cumulative ascending distribution

To construct a cumulative ascending distribution, the Custom Distribution needs its input in three columns: a list of minimum values, a list of maximum values and a list of cumulative probabilities, F(x), associated with those ranges. In addition, when you define the assumption, you have to specify that the data are cumulative by clicking the "Probabilities are cumulative" button as shown in the first figure below;


Crystal Ball then constructs a cumulative distribution by straight-line interpolation between the points defined on the curve. Using the data shown above, his will result in the cumulative distribution as shown below.

Model Crystal Ball Custom Distribution (sheet Cumul) provides the example shown above.

Option 6: Combining a Discrete Uniform, Discrete and Histogram distribution

To combine the Discrete Uniform, Discrete and Histogram distribution, or combine method 1 and method 2 of the Discrete distribution into one Custom Distribution, you have to use a three or four column format. The figure below shows an example for a 4-column format. This example assumes that there is a 10% chance of any value between 0 and 3, a 20% change of any value between 3 and 6, a 10% chance of any integer from 7 to 12, and 10%, 15%, 25% and 10% chance on respectively 13, 15, 17 and 19.

Note that when combining distributions or methods, the three column format with the middle column empty does not result in a General Distribution as described in the section above, but results in a discrete distribution.

Model Crystal Ball Custom Distribution (sheet Combination) provides the above example.

Combining two or three distributions as show below can be useful in situations where a parameter you want to model takes either specific (discrete) values or a certain range. The most obvious use is to model the impact of a risk. For example, an expert may estimate that the number of acres in a park that will be burned down because of forest fires next year is zero with a 60% probability (Discrete distribution), anywhere between 100 and 150 acres with a 30% probability and anywhere between 150 and 250 acres with a 10% probability (Histogram distribution).

The link to the Crystal Ball Custom Distribution model is provided here:  Crystal_Ball_Custom_Distribution

  • No labels