Probability Distribution Functions

Probability distribution functions are used for adding uncertainty to cells and equations in a spreadsheet model. For example, the following formula

=RiskUniform(10,20)

specifies that during a simulation, the cell that contains it will generate random uniformly distributed samples between 10 and 20.

Like most Excel functions, distribution functions can have arguments that reference cells or expressions, such as

=RiskTriang(B1,1.5*B2,B3)

This specifies a triangular distribution with a minimum value obtained from cell B1, a most likely value 1.5 times the value in cell B2, and a maximum value obtained from cell B3.

It is possible to construct complicated formulas that combine multiple distributions. For example, one such formula is

=100+RiskUniform(10,20)+(1.5*RiskNormal(A1,A2))

Alternate Distribution Functions

Many distribution functions can be specified with percentile values instead of their more traditional parameters. @RISK refers to this as an “alternate parameter” version of the distribution. For example, it might be more natural to assess the 10th and 90th percentiles a normal distribution instead of the usual mean and standard deviation.

The Alt (or AltD) versions of distribution functions provide this functionality. Each parameter of an alternate parameter distribution function requires two arguments. The first specifies the type of the parameter, while the second is its value. For example, the following function specifies a normal distribution with a 5th percentile of 67.10 and a 95th percentile of 132.89:

=RiskNormalAlt(5%, 67.10, 95%, 132.89)

Alternate parameters can be either percentiles or standard distribution arguments (or a combination). If the type of parameter argument is a label in quotes (such as “mu”), the parameter is the standard distribution argument with that name. This allows percentiles to be mixed with standard distribution arguments. For example, the following formula specifies a normal distribution with mean 100 and a 95th percentile of 132.89:

=RiskNormalAlt("mu", 100, 95%, 132.89)

The standard parameter names for each distribution can be found in the heading for each function in this documentation, or in the Excel Insert Function wizard.

Some distributions have an additional “location” parameter added when they are specified using alternate parameters. This parameter is added for distributions that do not have a built in location in their standard parameter list to allow the specification of percentiles for shifted distributions. For example, the RiskGamma function does not specify a location through its standard parameter set, so a location parameter is added in its alternate parameterization. In contrast, the RiskNormal function does have a location parameter in its standard first parameter (the mean), so it does not need an extra location parameter when using an alternate parameterization.

Alternate percentile parameters can be specified in terms of cumulative descending percentiles, as well as the standard cumulative ascending percentiles. Each of the Alt forms for probability distribution functions (such as RiskNormalAlt) has a corresponding AltD version (RiskNormalAltD). When the AltD version is used, any percentile values are cumulative descending percentiles.

Empirical Distribution Functions

There are several “empirical” distribution function which take one or two array parameters. They are called empirical because instead of taking a small set of parameters and then generating samples based on some theoretical mathematical model, they instead are free-form distributions defined by the data in the arrays. All these functions can be created and edited graphically using the Distribution Artist window.

These functions include:

  • RiskCumul/RiskCumulD – specifies a continuous distribution based on a set of cumulative (X,P) values.
  • RiskDiscrete – specifies a discrete distribution based on a set of (X,Y) values.
  • RiskDUniform - specifies a discrete distribution based on a set of X values.
  • RiskGeneral – specifies a continuous distribution based on an array of density (X,Y) values.
  • RiskHistogrm – specifies a continuous distribution based on an array of probability (P) values and a minimum and maximum.

Array parameters in Excel are denoted by either enclosing the numeric values of the array in curly brackets {} or by referencing a contiguous range of cells such as A1:C1. For example, these functions are both valid @RISK functions:

=RiskGeneral(0,1,{0.252,0.504,0.752},{0.542,0.843,0.742})

=RiskGeneral(0,1,A1:C1,A2:C2)

Please note, however, it is not possible to list cell references or names in arrays as one would list constants. For example, {A1,B1,C1} cannot be used to represent the array containing the values in cells A1, B1, and C1. Instead, use the cell range reference A1:C1 or enter the values of those cells directly in the arrays as constants, such as {10,20,30}.

Dates in @RISK Functions

@RISK supports the entry of dates in distribution functions. The RiskIsDate(TRUE) property function instructs @RISK to display graphs and statistics associated with the distribution using dates.

Typically date arguments to @RISK distribution functions are entered using references to cells that contain dates. For example, the following function could reference the date 10/1/2015 in cell A1, 1/1/2016 in cell B1 and 10/10/2016 in cell C1:

=RiskTriang(A1,B1,C1,RiskIsDate(TRUE))

To enter specific dates in @RISK distributions, it is necessary to use Excel functions to convert into Excel’s date system. For example, the function for the triangular distribution described above, but without using cell references, can be entered as:

=RiskTriang(DATE(2015,10,1),DATE(2016,1,1),DATE(2016,10,10),RiskIsDate(TRUE))

Not all arguments for all functions can logically be specified with dates. For example, functions such as RiskNormal(mu,sigma) require the mu parameter to be entered as a date, but not the sigma parameter. The Distribution Panel in the Define Distribution window shows the type of data (dates or numeric) that can be entered for each distribution type when date formatting is enabled.

Property Functions for Distributions

There are a large number of property functions that can be used to modify how a distribution works. The property functions appropriate for use with probability distributions can be broken down into a few categories:

See Property Functions for more of an overview of property functions and how they are used in @RISK functions.