Home → Techniques and Tips → @RISK Distributions → Distribution Parameters from "Alt" Distributions
Applies to:
@RISK 5.x–7.x
When I specify a distribution in terms of percentiles or "alt parameters", how does @RISK figure out the parameters of the distribution?
That's a good question. If you have a RiskPertAlt or RiskTriangAlt, for example, @RISK finds what parameters of a standard RiskPert or RiskTriang would give the percentiles you specified. But there's no formula. Instead, @RISK has to use a process of successive approximations to find the right parameters for the RiskPert. And it's the same for all the other Alt distributions, as well as RiskTrigen, which specifies two of a triangle's three parameters as percentiles. RiskUniformAlt is the exception; see below if you want to know the theory.
How can I find out what standard parameters @RISK computes for Alt functions?
For example, paste this formula into an empty cell:
=RiskBetaGeneralAlt(5%,-3,25%,2,75%,12,95%,16)
Press Enter, and then click Define Distributions. (As an alternative, you could click Define Distributions on an empty cell, select the distribution, and enter the parameters in the dialog.)
Click the drop-down arrow to the right of Alternate, remove the check mark for Alternate Parameters, and click OK just once. The display now shows the equivalent regular parameters, α1=1.295682, α2=1.121222, Min=-4.990886, Max=17.204968. (Because these are rounded values, some statistics and percentiles may be slightly different from their values in the Alt distribution.) The full non-Alt distribution is shown in the Cell Formula box near the top of the Define Distributions dialog:
=RiskBetaGeneral(1.295682,1.121222,-4.990886,17.204968)
If you now click OK, @RISK will replace the Alt distribution in your worksheet with that non-Alt distribution; if you click Cancel, the Alt distribution will remain in your worksheet.
I have to convert a number of Alt functions to standard parameters. Is there some way to do this with worksheet functions?
For some Alt functions, yes. The attached workbook gives examples.
If the standard parameters of a distribution are statistics like min, max, and mean, you can use RiskTheoMin and other "Theo" statistic functions to find those parameters. The triangular distribution, for example, has parameters of min, mode (most likely), and max, and you can get them by applying those "Theo" functions to the TriangAlt or Trigen.
If the parameters don't map directly to statistic functions, but there are formulas in the help file, you can solve those formulas to find the parameters. For instance, the help file says that the mean and variance of a BetaGeneral are
μ = min + α1(max−min)/(α1+α2)
σ² = α1α2 (max−min)² / ( (α1+α2)² (α1+α2+1) )
Solving for α1 and α2 gives
α2 = ( (μ−min)(max−μ)/σ² − 1 ) (max−μ) / (max−min)
α1 = α2 (μ−min) / (max−min)
The attached workbook shows about a dozen examples, mostly less complicated than that. Unfortunately, not all distributions have closed-form expressions for the statistics in terms of the distribution parameters; for those, the only choice is the method above using the Define Distribution window.
What about RiskUniformAlt? I'm curious how @RISK can use a formula to convert it to standard parameters, when the other Alt distributions require successive approximations.
This section shows the algebraic solution for those who are interested, although the techniques given above are quicker and simpler. Unlike all the other Alt functions, @RISK uses formulas to convert RiskUniformAlt to the equivalent non-Alt function. Consider RiskUniformAlt(C1,x1,c,x2) where C1 and C2 are cumulative ascending percentiles >0 and <1. How is that converted to the equivalent RiskUniform(min,max)?
The CDF (cumulative distribution function) for RiskUniformAlt is a straight line passing through your desired percentiles (x1,C1) and (x2,C2). But the same straight line also passes through (min,0) and (max,1), although you don't yet know the values of min and max. Therefore the equation of the CDF is
C = (x − min) / (max − min)
Substituting your two desired percentiles (x1,c1) and (x2,C2) gives C1⋅(max − min) = x1 − min and C2⋅(max − min) = x2 − min. Solving those as simultaneous equations in min and max gives the formulas
min = (x1⋅C2 − x2⋅C1) / (C2 − C1)
max = min + (x2 − x1) / (C2 − C1)
Additional keywords: Standard parameters, Alt parameters, Standard distributions, Alt distributions
Last edited: 2018-07-05