HomeTechniques and Tips@RISK DistributionsReplacing RAND with RiskBernoulli or RiskUniform

3.25. Replacing RAND with RiskBernoulli or RiskUniform

Applies to: @RISK, all releases

I use the Excel RAND function a lot in my spreadsheet, but it is causing some problems. For example, I am not getting the same results when I run my simulation a second time, even though I am using a fixed seed. And when I use shoeprint mode, the numbers in the worksheet are different from what the Data Window shows for the same iteration.

Use an @RISK distribution like RiskBernoulli or RiskUniform instead of Excel's RAND function. If you have a fixed random number seed, @RISK functions will produce a reproducible stream of random numbers. See Random Number Generation, Seed Values, and Reproducibility for more about this.

Sometimes people use RAND in an IF function to decide whether to draw a value from an @RISK distribution:

=IF( RAND()<0.4, RiskNormal(100,10), 0 )

The direct @RISK equivalent to Excel's RAND is RiskUniform(0,1):

=IF( RiskUniform(0,1)<0.4, RiskNormal(100,10), 0 )

However, RiskBernoulli is a simpler choice for IF-tests because it puts the probability right in the function:

=IF( RiskBernoulli(0.4), RiskNormal(100,10), 0 )

You can simplify this expression even further. Since RiskBernoulli returns a 0 or 1, you can replace the IF with a multiplication:

=RiskBernoulli(0.4) * RiskNormal(100,10)

All four of these formulas say that a given risk is 40% likely to occur, and if it does occur it follows the normal distribution. But the @RISK functions give you a reproducible simulation, which RAND does not.

Which of those is the recommended way to model an event risk or operational risk?

The last one is the simplest, but all four have the same problem: you're modeling one risk with two distributions. This means that sensitivity measures won't be accurate, and graphs of simulated results will either show a lot of errors or show a lot of values that weren't actually used. To solve all of these problems, you want to wrap the expression in a RiskMakeInput, like this:

=RiskMakeInput( RiskBernoulli(0.4) * RiskNormal(100, 10) )

See Event or Operational Risks for more information about using RiskMakeInput in this way.

Additional keywords: Bernoulli distribution, MakeInput distribution, Uniform distribution

Last edited: 2019-02-15

This page was: Helpful | Not Helpful