Home → Techniques and Tips → @RISK Distributions → Cutting Off a Distribution at Left or Right
Applies to: @RISK 5.x–7.x
I have a regular distribution, but I want to truncate one tail. For example, maybe I have a RiskNormal(50,10) and I want to ensure that it never goes below 0.
The RiskTruncate property function limits the sampling of a distribution. Specify only a lower bound, only an upper bound, or both lower and upper bounds. With any such truncation, the "lost" probability is redistributed proportionally across the remaining range of the interval. This is better than using Excel's MIN and MAX functions, which distort the distribution by taking all the probability beyond the truncation point and adding it to the truncation point.
You can set truncation limits by editing formulas, or in the Define Distribution dialog (later in this article). Either way, the limits can be fixed numbers or cell references, although the examples in this article all use fixed numbers.
To specify only a minimum, with maximum unbounded, just omit the maximum argument of the RiskTruncate function. For example, in the RiskTruncate function below, the minimum has been specified as 0, but the maximum is +∞:
RiskNormal(10, 5, RiskTruncate(0, ) )
The comma is optional when you specify only a minimum:
RiskNormal(10, 5, RiskTruncate(0) )
Likewise, you can specify only a maximum, with minimum unbounded, by omitting the minimum argument. (Notice the required comma before the maximum.) This example specifies a minimum of −∞ and maximum of 15:
RiskNormal(10, 5, RiskTruncate(, 15) )
Finally, you can specify both minimum and maximum, by supplying both arguments. This example specifies a minimum of 2 with a maximum of 15:
RiskNormal(10, 5, RiskTruncate(2, 15) )
You're not limited to naturally unbounded functions; you can also truncate a bounded function like a RiskTriang. For example, if you want a RiskTriang(100,200,300) shape, but with no values above 250, code it this way:
RiskTriang(100, 200, 300, RiskTruncate(, 250) )
If you prefer not to edit property functions in formulas, you can enter one-sided or two-sided truncations in the Define Distribution window:
Right-click the Excel cell with the distribution function you want to truncate, and choose @RISK » Define Distributions from the popup menu; or, left-click the cell and then click the Define Distributions icon in the ribbon. The Define Distribution dialog appears.
In the left-hand section of the dialog, find the Parameters entry and click into the box that says Standard.
A drop-down arrow appears at the right of that box; click the arrow.
Check (tick) the "Truncation Limits" box, and select Values or Percentiles at the right. Click OK.
Specify a minimum by entering it in the box labeled "Trunc. Min", or specify a maximum by entering it in the box labeled "Trunc. Max". Again, these can be fixed numbers or cell references. If you leave the minimum empty, the distribution will not be truncated at left; if you leave the maximum empty, the distribution will not be truncated at right.
As soon as you enter either a minimum or maximum, the RiskTruncate function appears as an argument of the distribution function in the cell formula displayed at the top of the Define Distribution window. Because the default minimum parameter is −∞, and the default maximum parameter is +∞, the parameter you do not specify is automatically omitted from the RiskTruncate function.
Click the OK button to write the formula to Excel.
If you often use truncation limits in your distributions, you can configure @RISK to make the "Trunc. Min" and "Trunc. Max" boxes a regular part of the Define Distributions dialog box. In @RISK, click Utilities » Application Settings » Distribution Entry and change Truncation Limits to Always Displayed (Values) or Always Displayed (Percentiles).
See also:
Last edited: 2016-09-01