Applies to: @RISK 5.x and Up

 

How to Truncate a Distribution in @RISK

Question:
I have a regular distribution but want to truncate one or both tails. For example, I have RiskNormal(50,10) and want to ensure it never goes below 0. How can I do this?

Answer:
Use the RiskTruncate property function to limit the sampling of a distribution. You can specify a lower bound, upper bound, or both. With truncation, the “lost” probability is redistributed proportionally across the remaining range, which preserves the shape better than using Excel’s MIN or MAX functions.

You can set truncation limits in formulas or via the Define Distribution dialog. Limits can be fixed numbers or cell references.

1. Truncating in Formulas

• Only minimum (maximum unbounded):

RiskNormal(10, 5, RiskTruncate(0))

or

RiskNormal(10, 5, RiskTruncate(0, ))

• Only maximum (minimum unbounded):

RiskNormal(10, 5, RiskTruncate(, 15))

• Both minimum and maximum:

RiskNormal(10, 5, RiskTruncate(2, 15))

• Truncate a bounded distribution:
  For example, truncate a RiskTriang(100,200,300) at 250:

RiskTriang(100, 200, 300, RiskTruncate(, 250))

2. Truncating via the Define Distribution Dialog

1. Open Define Distribution:

   • Right-click the Excel cell with the distribution and choose @RISK → Define Distributions,
     or left-click the cell and click the Define Distributions icon in the ribbon.

2. Set Parameters:

   • In the left-hand section, click into the Parameters box labeled Standard.

   • Click the drop-down arrow and check Truncation Limits.

   • Select Values or Percentiles.

3. Specify Min/Max:

   • Enter a value in Trunc. Min or Trunc. Max, or leave blank for no truncation on that side.

   • Once entered, the RiskTruncate function automatically appears in the formula.

4. Apply Changes:

   • Click OK to write the formula to Excel.

3. Always Display Truncation Limits

If you use truncation often, you can configure @RISK to always show Trunc. Min and Trunc. Max in the Define Distributions dialog:

• Click Utilities → Application Settings → Distribution Entry,

• Set Truncation Limits to Always Displayed (Values) or Always Displayed (Percentiles).

 

Related Articles:

Statistics for Just Part of a Distribution

Truncate and Shift in the Same Distribution