Home → Techniques and Tips → @RISK Distributions → Truncate and Shift in the Same Distribution
Applies to: @RISK for Excel, all releases
My @RISK distribution function is not obeying the minimum and maximum set by the truncation function. Here is the function I am using:
=RiskPearson5(47, 6018, RiskShift(-78), RiskTruncate(11,100))
But in a simulation, I am getting many values below 11. What is the problem?
We tend to think of truncation as applying limits after shifting. However, when simulating a distribution function, @RISK always truncates first and then shifts, regardless of the order of these arguments in the distribution function. In your example, after truncating at 11 and 100, @RISK shifts the distribution left 78, so that the actual min and max for your Pearson5 are –67 and 22.
You need to take this into account when figuring out how to manipulate the function to get the desired result. Subtract your desired shift factor from your desired final limits for the distribution.
For example, if you want a Pearson5 that is truncated at 11 and 100 after shifting left by 78 units, compute the pre-shift truncation limits as 11–(–78) = 89 and 100–(–78) = 178, and code your function this way:
=RiskPearson5(47, 6018, RiskShift(-78), RiskTruncate(89,178))
The truncation limits 89 to 178 before shifting become your desired limits 11 to 100 after shifting.
See also:
Last edited: 2015-06-19