Home → Techniques and Tips → @RISK Simulation: Numerical Results → How @RISK Calculates Percentiles
Applies to:
@RISK Excel 3.5 through 7.x
@RISK for Project 3.x, 4.x
RISKOptimizer 1.x, 5.x
@RISK Developer's Kit (RDK) 4.x
RISKOptimizer Developer's Kit (RODK) 4.x
How does @RISK calculate cumulative percentiles for simulation data?
Depending on the nature of the simulation data, @RISK will use one of two methods for calculating cumulative percentiles.
When the simulation data appear to be discrete (samples are repeated in the data), every returned percentile is chosen from the simulation data. Specifically, the software computes k = the smallest whole number greater than or equal to (your percentile target) times (number of iterations), and then the answer is the k-th smallest data value from the simulation. For a simplified example, suppose you request the 68th percentile from a simulation where there were ten iterations and the data points were {4, 7, 9, 13, 15, 19, 21, 25, 28, 30}. k = roundup(.68*10) = 7, so the 68th percentile is the 7th-lowest number, which is 21.
When the simulation data appear to be continuous (none of the samples are repeated in the data), @RISK will use linear interpolation to calculate percentiles where necessary. For example, when the desired percentile does not correspond exactly with a value in the data @RISK will use linear interpolation between points in the data set to derive the percentile. See the attached spreadsheet demonstrating the linear interpolation.
Does @RISK's calculation correspond to the Excel function PERCENTILE.INC( ) or PERCENTILE.EXC( )?
You can specify any number from 0 to 1 inclusive as the second argument of RiskPtoX( ), and RiskTheoPtoX( ), so to that extent they are analogous to PERCENTILE.INC( ). However, it may be necessary to interpolate to find the value of a given percentile. Excel and @RISK may not necessarily return the same values, based on their different interpolation methods. (The literature showsn numerous methods of interpolation.) The larger the number of iterations, the smaller should be any difference between the two.
RiskPtoX(A1,0) and RiskPtoX(A1,0) equal the smallest and largest iteration values of cell A1 in the latest simulation. RiskTheoPtoX(A1,0) returns the theoretical minimum of the distribution in A1 if it has a lower bound, or #VALUE! if there's no lower bound. RiskTheoPtoX(A1,0) returns the theoretical maximum, or #VALUE! if the distribution has no upper bound.
Last edited: 2017-09-27