Home → Techniques and Tips → @RISK Simulation: Numerical Results → Correlation Coefficient of Output Distributions
Applies to:
@RISK for Excel, releases 5.x–7.x
I want to find the simulated correlation coefficient between two cells in my simulation. Is there any way to find this value?
In @RISK 5.5 and newer, the RiskCorrel( ) function can compute the correlation for you, either the Pearson correlation or the Spearman (rank-order) correlation. To find the simulated Pearson correlation, enter this function in a worksheet cell:
=RiskCorrel(cell1, cell2, 1)
To find the simulated Spearman (rank-order) correlation, use:
=RiskCorrel(cell1, cell2, 2)
The cell will show #VALUE initially, replaced with the coefficient when you run a simulation.
When you specify your desired correlation for two inputs, as opposed to computing the actual correlation in a simulation, @RISK applies Spearman correlation of those inputs. See Excel Reports a Correlation Different from What I Specified for more about this.
How was this done in older releases of @RISK?
These methods continue to work in newer releases of @RISK, though the RiskCorrel( ) function is simpler to use.
In @RISK 5.0, you can display the correlation coefficient fairly easily by making a scatter plot.
In the Results Summary window, select one of the outputs and click the icon for "Create Scatter Plot" at the bottom of the dialog box. Then drag the other output from the Results Summary window onto the scatter plot. You get a scatter plot, the mean and standard deviation of both outputs, and the Pearson correlation coefficient. (Y is the first output you selected, and X is the second output you selected.)
You can even drag additional outputs to the Scatter Plot window, and they will be plotted as additional X's against the same Y, with their correlation coefficients displayed
In @RISK 5.0, if you want the correlation coefficient to appear automatically in an Excel sheet after simulation, use the RiskData( ) function to insert data in a worksheet during simulation and use Excel's CORREL( ) function. Please see Placing Iteration Data in Worksheet with RiskData( ) and Sum of All Iteration Values for two examples using RiskData( ).
If you have a large number of iterations, RiskData( ) may slow down the simulation for some models. If this is an issue for your particular model, you can remove the RiskData( ) functions and use a macro to save the simulation data. Please see Exporting Information During Simulation for an example.
last edited: 2018-08-29