Home → Techniques and Tips → @RISK Simulation: Numerical Results → How Many Iterations Were within a Certain Range?
Applies to:
@RISK 4.x–7.x
I'd like to know how many iterations, or what percent of iterations, had a particular input or output between two limits. I know I could do this through filtering, or through moving the delimiters in a Browse Results graph, but is there a worksheet function?
Yes, you can do this with worksheet formulas. The basic idea is that
(number of iterations in range) =
[ (right percentile) − (left percentile) ] × (total number of iterations)
@RISK provides the pieces you need for that formula, and the process is the same for an input or an output.
Please take a look at the attached example. The numbers you can change are in blue on white; the formulas in other cells can be viewed but not changed.
The method, starting from x-values:
Suppose you'd like to know how many iterations saw a profit (cell C9) between $22,000 and $23,000 (cells F9 and G9). Referring to the formula above, you see that you need to ask which percentiles those limits represent. RiskXtoP will tell you that.
The percentage between F9 and G9 is the difference of those percentiles. (The percentage between F9 and G9 is the part that is below G9 but is not also below F9.)
Multiply that by the total iterations from cell H2, and round to an integer. (See Placing Number of Iterations in the Worksheet.) You need to round the result, si that you don't end up with fractional iterations, because the RiskXtoP functions interpolate their values between the iterations that actually occur in any particular simulation.
The formula was "exploded" into multiple cells to show the steps. But you can do all of it in one formula; see columns M–P.
The method, starting from percentiles:
If you want the number of iterations between two stated percentiles, you don't need the RiskXtoP functions. Rows 11–13 show the formulas broken down into bits, and in one cell.
See also: For tracking logical values instead of computed inputs or outputs, see How Many Times Did an Event Occur?
Last edited: 2018-05-08