Home → Techniques and Tips → @RISK Simulation: Numerical Results → How Many Times Did an Event Occur?

6.21. How Many Times Did an Event Occur?

Applies to: @RISK 6.x/7.x

We have a combined risk register that models the risks based on Monte Carlo sampling. I would like to get a table that shows how many times risk one, risk two, risks one and three, or risks one-two-three occurred.

This is a special case of a more general problem: in how many iterations did a given event or combination of events occur? Or, instead of how many iterations, you might want to know in what percentage of iterations some event occurred.

The basic technique is to construct a cell formula that is 1 when a desired event occurs and 0 when it doesn't. Constructing that formula is not hard if you know these rules:

Use parentheses around each condition, to avoid problems with order of operations. If you're tracking when G7 is 120 or more, for instance, code it as (G7>=120), not plain G7>=120.
If you're tracking a simple event, as opposed to a combination, add a 0 to it: =(G7>=120)+0, not =(G7>=120). This doesn't affect the final results, but it keeps this cell from showing as TRUE or FALSE when combinations show as 1 or 0.
To join conditions with AND, simply multiply them. =(G7>=120)*(P22<11) is 1 (true) when G7 is at least 120 and P22 is less than 11. If G7 is below 120 or P22 is at least 11, or both, the formula is 0 (false).
To join conditions with OR is a little bit more complicated. You can't just use + because the expression would then be 2, not 1, if both conditions are true. Probably easiest to read is this format: =0+OR(G7>=120,P22<11). This returns 1 (true) if G7 is at least 120 or P22 is under 11, or 0 if G7 is under 120 and P22 is at least 11. You don't need parentheses around the conditions, because the comma separator avoids problems with order of operations.

An example is attached to this article. It uses part of a sheet from our standard Risk Register example, in rows 1 to 9. The green box tracks seven events, showing how to compute the percentage of iterations where each event occurred, as well as the number of iterations where each event occurred.

See also: This is simpler with numeric data, as explained in How Many Iterations Were within a Certain Range?

Last edited: 2017-03-30

Downloads

xls
KB1508 Count Events with Combinations.xlsx

This page was: Helpful | Not Helpful