Uses of RiskMakeInput()

An Alternative to multiplying RiskBinomial() by my RiskTriang()

 

Multiplying a distribution by an occurrence probability isn’t the same as using an extra distribution. With plain multiplication, all 10,000 simulated values of the underlying distribution exist, but only the 40% used are meaningful—yet statistics still reflect all 10,000.

With RiskMakeInput, the product becomes the distribution itself: 6,000 zeros and 4,000 real values, so the stats match reality.

Example:

If the probability is in cell C11 and impact in C12, your function for actual impact in any given iteration would look like this:

=RiskMakeInput(C11*C12)

If you wish, you can give it a name:

=RiskMakeInput(C11*C12, RiskName("my name for this risk") )

RiskMakeInput also fixes tornado graphs: instead of separate or misleading bars for the occurrence and the impact distributions, you get a single, accurate bar when the risk matters.

NOTE:  RiskMakeInput is not able to do correlations.  See the workaround in Approximate Correlations of RiskMakeInput or RiskCompound

 

 

An Alternative to IF statements

Let's say the current formula is below:

=IF(C47=1,RiskBinomial(D47,E47),
 IF(C47=2,RiskPert(F47,G47,H47),
 IF(C47=3,RiskLognorm(I47,J47),
    RiskTriang(K47,L47,M47))))

Using nested IFs to switch between distributions is inefficient. It creates many unwanted entries in the Model Window and Simulation Data, shows errors for unused distributions, and slows simulations by evaluating multiple distributions every iteration.

A quick workaround is to wrap the entire formula in RiskMakeInput() to stop @RISK from collecting spurious data:

=RiskMakeInput(
   IF(C47=1,RiskBinomial(D47,E47),
   IF(C47=2,RiskPert(F47,G47,H47),
   IF(C47=3,RiskLognorm(I47,J47),
      RiskTriang(K47,L47,M47))))
)

This cleans up sensitivity handling but does not improve efficiency.

A cleaner and faster solution is to move the logic into a VBA macro that writes the selected distribution directly into the cell once, instead of recalculating multiple distributions each iteration.

The macro checks the chosen distribution, writes the correct function and parameters, applies a RiskName, and then runs the simulation. This avoids extra distributions, errors, and performance overhead.

The referenced example shows a setup where a single choice column specifies the distribution, the macro writes the appropriate @RISK function, and the model runs cleanly.

Aggregating Multiple Inputs

If you already have a formula that calculates the metric you want, wrap it in RiskMakeInput():

=RiskMakeInput(formula, RiskName("name for tornado chart"))

(Or place the formula in an empty cell and wrap it there.)

RiskMakeInput() tells @RISK to treat that formula as an input for sensitivity analysis and to ignore its precedents when calculating sensitivities.

Key implications:

• Precedent tracing stops at the RiskMakeInput(). Any distributions feeding into that formula are excluded from all sensitivity calculations for all outputs.

• Even RiskMakeInput() functions inside those precedents are excluded.

• The RiskMakeInput() does not need to be an Excel precedent of the output. If the output and the RiskMakeInput() share any precedents, @RISK treats the RiskMakeInput() as a precedent for sensitivity purposes.

• It affects all sensitivity measures, including graphs and functions like RiskSensitivity() and RiskSensitivityStatChange().

Example files can be found under:

• Help → Example Spreadsheets → Statistics/Probability → Using RiskMakeInput Function

 

 

For Excluding Inputs

To exclude inputs from tornado and other sensitivity results, wrap them in a simple RiskMakeInput() placed in any empty cell:

=RiskMakeInput(P11+J15)

All precedents of that RiskMakeInput are then ignored in sensitivity analysis, even without changing your model.

To keep the RiskMakeInput itself from appearing as an input, force it to a constant:

=RiskMakeInput(0*(P11+J15))

This works whether Smart Sensitivity Analysis is on or off.

If you only want to hide bars (without recalculation), right-click the bar in the tornado and choose Hide Bar; use Restore Hidden Bars to bring them back. (Not available for spider graphs.)

 

 

Related Article:

Combining Frequency and Severity (For risks that could happen multiple times in one iteration)