HomeTechniques and Tips@RISK DistributionsRAND( ) Versus RiskUniform( )

3.22. RAND( ) Versus 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.

Use a RiskUniform(0,1) instead of the RAND function. It does the same thing, but it respects the random number stream used by @RISK.  If you have a fixed random number seed, the RiskUniform( ) 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. Aside from the inability to reproduce results, you will get Error notations for the RiskNormal( ) distribution the other 60% of the time, and sensitivity analysis will show dependencies on the RiskNormal( ) instead of the expression. This can better be modeled using RiskCompound and RiskBinomial:

=RiskCompound(RiskBinomial(1, 0.4), RiskNormal(100, 10))

At every iteration, RiskCompound( ) draws a value from RiskBinomial( ); 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 RiskBinomial( ) returns a 0, RiskCompound( ) has a value of 0. Sensitivity analysis treats RiskCompound( ) as an input, not RiskBinomial( ) or RiskNormal( ).

Additional keywords: Binomial distribution, Compound distribution, Uniform distribution

Last edited: 2015-06-19

This page was: Helpful | Not Helpful