HomeTechniques and Tips@RISK Simulation: Numerical ResultsWhich Iteration Produced a Given Percentile?

6.15. Which Iteration Produced a Given Percentile?

Applies to: @RISK for Excel, all releases

I know how to find the value of a percentile, such as the 99th percentile. But how can I find which iteration produced the 99th percentile of a given input or output? I want to look at that whole scenario.

The easiest way is to open the Simulation Data window, highlight the column for that input or output, click the sort icon at the bottom of the window, and sort in descending order. Then count down the appropriate number of iterations and you have the one you need.

For example, if your simulation runs 1000 iterations, then your 99th percentile would be the 11th highest one, which is the highest of the bottom 990 iterations.

If this is a frequent need, you could automate the process with a RiskData( ) array function and a VBA macro. Please see Placing Iteration Data in Worksheet with RiskData( ).

Last edited: 2015-01-21

This page was: Helpful | Not Helpful