Alternatives to Excel’s RAND function

 

Problem
Excel’s RAND() function generates random numbers using Excel’s own random number stream, which is not controlled by @RISK.

• This makes simulations non-reproducible, even when a fixed seed is set in @RISK.

 

Solution
Use @RISK distributions such as RiskUniform or RiskBernoulli instead of RAND(). These functions are controlled by @RISK’s random number generator and respect fixed seed settings, ensuring reproducible results.

Direct replacement for RAND
The direct @RISK equivalent of RAND() is:
RiskUniform(0,1)

For example, an Excel formula like:
IF(RAND() < 0.4, RiskNormal(100,10), 0)
can be written as:
IF(RiskUniform(0,1) < 0.4, RiskNormal(100,10), 0)

Using RiskBernoulli for event logic
RiskBernoulli is often a better choice for IF tests because it explicitly includes the probability:
IF(RiskBernoulli(0.4), RiskNormal(100,10), 0)

Since RiskBernoulli returns either 0 or 1, the IF statement can be simplified to:
RiskBernoulli(0.4) * RiskNormal(100,10)

All of these approaches model an event that has a 40% chance of occurring and, if it occurs, follows a normal distribution.

Unlike RAND(), the @RISK-based approaches are reproducible when a fixed seed is used.

Recommended modeling approach
Although the simplified multiplication form is convenient, all of the above examples model a single risk using two separate distributions.

• This can cause inaccurate sensitivity measures and misleading graphs.
• The recommended approach is to wrap the expression in RiskMakeInput so @RISK treats it as a single input:
  RiskMakeInput(RiskBernoulli(0.4) * RiskNormal(100,10))

This ensures correct sensitivity analysis and cleaner simulation results.

 

 

Related Articles:

Random Number Generation, Seed Values, and Reproducibility

Event or Operational Risks