HomeTechniques and Tips@RISK DistributionsRAND versus RiskUniform or RiskBernoulli

3.24. RAND versus RiskUniform or RiskBernoulli

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.

Use an @RISK distribution instead of Excel's RAND function. If you have a fixed random number seed, @RISK functions will produce a reproducible stream of random numbers.

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 idea is that a given risk is 40% likely to occur, and if it does occur it follows the normal distribution. But there are problems with this approach. Aside from being unable to reproduce results, you will get Error notations for the RiskNormal distribution the other 60% of the time, and sensitivity analysis will show dependencies of your outputs on the RiskNormal instead of the expression.

The @RISK equivalent to Excel's RAND is RiskUniform(0,1), but this IF function can be modeled more simply with RiskCompound and RiskBernoulli:

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

At every iteration, RiskCompound draws a value from RiskBernoulli. 40% of the time that value is 1, and the other 60% it is zero. When the value is 1, RiskCompound( then draws a value from RiskNormal, and that is the value of the whole formula for this iteration. In iterations when RiskBernoulli returns a 0, RiskCompound has a value of 0. Sensitivity analysis treats RiskCompound as an input, not RiskBernoulli or RiskNormal, which gives you more realistic tornado graphs.

See also: All Articles about RiskCompound

Additional keywords: Bernoulli distribution, Compound distribution, Uniform distribution

Last edited: 2018-06-28

This page was: Helpful | Not Helpful