See also: Multivariate_Normal_distribution ,  VoseCorrToCov, Vose_Correlation_Matrix




Example model

The covariance between two random variables X and Y is defined as:

where E[ ] means the expected value, and X, Y refer to the respective means of X and Y.

The size of Cov(X,Y) depend on the degree to which the variables deviate from their respective means. Pearson’s correlation coefficient XY normalises the covariance to be independent of this variation, as follows:

A covariance matrix is a square matrix of dimension n giving the covariance between each i,j pair of variables (I = 1 to n, j = 1 to n). The following table gives an example of a covariance matrix for the variables A to E:

The diagonal in red gives the covariance where X = Y, which is

which is the definition of variance. Thus a covariance matrix gives us both the Pearson correlation coefficient for each pair, and the variance for each variable.

VoseCovToCorr is an array function that extracts the correlation information from a covariance matrix. For example:

The cell range C11:G15 contains the correlation matrix. The top left to bottom right diagonal elements equal 1 meaning that a variable is 100% correlated with itself. The elements in opposite positions from the line of 1's are the same, meaning that variable X is correlated to Y to the same extent that Y is correlated to X.



Monte Carlo simulation in Excel. Learn more

Spreadsheet risk analysis modeling


Adding risk and uncertainty to your project schedule. Learn more

Project risk analysis


Enterprise Risk Management software (ERM)

Learn more about our enterprise risk analysis management software tool, Pelican

Enterprise risk management software introduction


For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now


For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now