HomeTechniques and Tips@RISK Simulation: Numerical ResultsCorrelation Coefficient of Output Distributions

6.13. Correlation Coefficient of Output Distributions

Applies to:
@RISK for Excel, all releases

Question:
I want to find the simulated correlation coefficient between two cells in my simulation. Is there any way to find this value?

Answer (@RISK 5.5 and later):
The RiskCorrel( ) function can compute the correlation for you, either the Pearson correlation or the Spearman (rank-order) correlation. The methods given below for earlier versions are no longer needed, but still work in @RISK 5.5.

Answer (@RISK 5.0):
While there was no native worksheet function in @RISK 5.0 to compute the correlation of two simulated outputs, you can display the figure 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.

Answer (@RISK 4.5 and earlier):
There is no native @RISK function to display the correlation of two simulated outputs. However, you can find this figure by opening the Data window, copy/pasting the iterations of the two outputs into an empty Excel sheet, then using Excel's CORREL( ) function.

Automated solution (all versions):
The RiskCorrel( ) function mentioned above for @RISK 5.5 and later can be entered in your Excel worksheet.

For @RISK 5.0 and earlier, 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: 2012-08-23

This page was: Helpful | Not Helpful