Statistic Functions
A statistic function returns an @RISK statistic calculation directly to a spreadsheet cell. These statistic functions fall in three broad categories:
- Theoretical – statistical properties of probability distributions, computed without reference to a simulation.
- Simulated Result – statistical properties of simulated inputs or outputs, computed during or after a simulation.
- Six Sigma – Six sigma results computed for simulated outputs during or after a simulation.
Statistic functions work by making a reference to another cell that contains the “target” of the statistical operation. For example, if cell A1 contains an output, the function
=RiskMean(A1)
will return the simulated mean of that output at the end of the simulation. Generally, it is a best practice to make all statistic functions target a model’s inputs or outputs. However, simulated result statistics can be calculated for any cell, even if not explicitly marked as an input or output. Effectively, when @RISK see such a situation it will make an implicit output for that cell.
The target of a statistics function can be specified using a text name instead of a cell reference. If that case, @RISK first checks for an output with that name. If none exists, @RISK then looks for an input probability distribution with the name and, if one is found, it returns the appropriate statistic for the samples drawn for that input. The burden is on the modeler to insure that unique names are given to outputs and inputs referenced in statistics functions.
Simulated result and Six Sigma statistic functions have an optional simulation number argument that controls which simulation should be used for statistical calculations when multiple simulations are run.
Theoretical Statistic Functions
Theoretical statistic functions return theoretical statistics for probability distributions. They can be calculated without running a simulation because they rely on the known properties of distribution functions.
For example, if cell A1 contains the function
=RiskNormal(11,2)
The function
=RiskTheoMean(A1)
Will return the value 11.
If multiple distribution functions are present in the target cell of a theoretical statistics function, @RISK will return the statistic for the last function in the formula.
Simulated Result Statistic Functions
Simulated result statistic functions return statistics based on the sample values collected during a simulation.
Six Sigma Statistic Functions
Six Sigma functions return Six Sigma statistics for simulated results in the target cell. All of the statistics functions in this section rely on a RiskSixSigma property function to supply values such as LSL and USL. This property function must be included in either the targeted RiskOutput function or in the Six Sigma statistics function itself (or both).
For example, suppose cell A10 is an output cell with the formula
=RiskOutput(“Width”,,,RiskSixSigma(90,96,93,1.5,6))+A6-A5
Then the following statistic function returns the Cpk statistic for the simulation results of that output:
=RiskCpk(A10)
The Cpk function doesn’t require a RiskSixSigma property function because it refers to an output cell that itself contains a RiskSixSigma property function. However, suppose the output in cell A10 is defined simply as:
=RiskOutput(“Width”)+A6-A5
Then the RiskCpk statistics function would require its own RiskSixSigma property function:
=RiskCpk(A10,RiskSixSigma(90,96,93,1.5,6))
It is possible to include the RiskSixSigma property function in both the RiskOutput function and the statistics function. In this case, the parameters in the latter override the parameters in the former.
If a cell reference is entered as the first argument of a Six Sigma statistics function, the target cell does not need to contain a RiskOutput function. However, in this case, the statistics function will require a RiskSixSigma property function.
Updating Statistic Functions during a Simulation
@RISK’s statistics functions can be updated either at the end of a simulation (the default) or after each iteration of a simulation. In most cases, statistics do not need to be updated until the end of a simulation, and by not calculating them each iteration the simulation process will often run considerably faster.
However, if the calculations in a model require a new statistic to be returned in each iteration (for example, when a custom convergence calculation has been entered using Excel formulas) this option can be changed using the Update Statistic Functions option on the Sampling tab of the Simulation Settings dialog.
Property Functions for Statistics
Many statistics functions allow either the RiskTruncate or RiskTruncateP property function. This will cause the statistic to be calculated on the truncated set values specified by the property function. For example, to calculate the mean of the upper 10th percentile of an output in cell A1, enter the function:
=RiskMean(A1,RiskTruncateP(0.9,1))