Home → Techniques and Tips → Correlation in @RISK → Correlating RiskMakeInput or RiskCompound, Approximately

5.7. Correlating RiskMakeInput or RiskCompound, Approximately

Applies to:
@RISK 5.x–7.x

The help file says that RiskCompound or RiskMakeInput can't be correlated, but I really need to use correlation in my model. Is there any workaround available?

You can come close, and the process is the same for RiskMakeInput or RiskCompound. In brief: (1) Simulate your RiskCompound or RiskMakeInput to find its percentiles. (2) Turn that set of percentiles into a new RiskCumul distribution. (3) Replace the RiskCompound or RiskMakeInput with the new RiskCumul, which you can correlate.

This technique is workable if the parameters of the RiskMakeInput or RiskCompound don't change from one simulation to another. If they do, this technique isn't practical. (You could, however, use the @RISK XDK to automate the process, in a before-simulation macro.)

Here are details of the procedure. (Please open the attached workbook and run a simulation.)

Step 1. Get a lot of percentiles.

RiskCumul needs the minimum, the maximum, and some percentiles. The attached workbook is already set up to find every half-percentile in cells G8:H208.

This example finds every half-percentile: P_0.5, P₁, P_1.5, and so on to P_99.5. Depending on your distribution, you might need more percentiles, or fewer percentiles might be enough, or you might need more percentiles in one region of the distribution but fewer percentiles in another region of the distribution.
This example runs 100,000 iterations to find those statistics, but if your distribution is highly irregular you might need more.

At the end of this preliminary simulation, the RiskCumul functions no longer show #VALUE, because the percentiles of the RiskMakeInput and RiskCompound are now available. But you can't graph the RiskCumul functions at this stage, because the percentiles weren't available during the preliminary simulation.

Step 2. Turn the percentiles into a RiskCumul.

After a simulation, all the percentiles are formulas. But you want to use them as values, without depending on the original RiskMakeInput or RiskCompound.

Highlight the percentiles array with your mouse. Press Ctrl+C for copy, then Alt+E, S, V, Enter for Paste Special: Values. The RiskCumul distribution is now independent of the original RiskCompound or RiskMakeInput.

Step 3. Replace RiskMakeInput or RiskCompound in your model with the new RiskCumul.

To do this, click the cell containing the RiskCumul, highlight the formula in the formula bar with your mouse, and press Ctrl+C then Esc. Click the cell that you want to replace, and press Ctrl+V then Enter. This copies the formula without changing the receiving cell's formats.

You can now correlate the RiskCumul in the usual way.

See also:

Correlation of RiskCompound for alternative workarounds to correlating RiskCompound.
All Articles about RiskMakeInput
All Articles about RiskCompound

Last edited: 2018-07-03

Downloads

xls
KB1650_RMIorRC_to_RiskCumul.xlsx

This page was: Helpful | Not Helpful