Home → Techniques and Tips → Correlation in @RISK → Correlation Matrix Exceeds Excel's Column Limit
Applies to: @RISK 4.x–7.x
I'm using Excel 2003, and I have a correlation matrix whose size exceeds the 256-column limit in Excel. How can I use a correlation matrix of this magnitude with @RISK?
Rebuilding the structure of the matrix lets you use a correlation matrix of this size with @RISK. Below is a description of how to rebuild the matrix. Also, attached are two examples that illustrate the transformation and referencing of the rebuilt matrix.
Note: @RISK 4.5.7 and earlier won't be able to run a simulation on the example models, because the matrices don't exceed the column limit in Excel. If you try a simulation, these older versions of @RISK will display the error message, "The correlation matrix [matrix reference] is not square." (See this article if you have a Excel 5.0 or newer.) When you have a matrix that does exceed the column limit, you won't get this error message. The attached examples are for illustrative purposes only, as regarding those older versions.
To rebuild the matrix, take the following steps:
Break your original matrix up into smaller blocks, moving from left to right. The number of columns in the blocks should be less than Excel's column limit, and each block should have the same number of rows as the original matrix. Make as many as the blocks the same size as possible.
For example, if you have a 400 x 400 correlation matrix, you could break it up into two blocks, each with 400 rows and 200 columns. A 789 x 789 correlation matrix could be broken up into three blocks with 789 rows and 250 columns each, and one block with 789 rows and 39 columns.
Stack the blocks vertically to create a new matrix.
Move from left to right, placing each block under the one before it. Place the second block under the first block, place the third block under the second block, and so on. See Example 1 in attached file.
The last block may have fewer columns than the others. That last and smallest block should always be placed at the very bottom of the stack. See Example 2 in attached file.)
Define a range name for the rectangle that contains the rebuilt matrix. The matrix cell range reference must be rectangular; it can't have an irregular shape. If you end up with one section of the matrix that has fewer columns than the rest, make the matrix cell range reference rectangular by including empty cells in the reference. See Example 2 in attached file.
For example, that 789 × 789 correlation matrix is rebuilt as three blocks 789×250 and one block 789×39, so you define your range name for the resulting rectangle of 3156 rows and 250 columns.
Add the RiskCorrmat function to your inputs. In your Excel workbook, add the RiskCorrmat function directly to each cell containing the input functions that you wish to correlate. The syntax for the RiskCorrmat function is:
RiskCorrmat (matrix cell range, position, instance)
I'm running Excel 2007 or later, which allows a million rows, so I don't need this technique for new models. But I've still got some older models that used this technique. Do newer versions of @RISK still support it?
Yes, this technique works in any type of Excel workbook — XLS, XLX, XLSM, etc. — in any supported Excel, for any @RISK release 4.x–7.x. (Although Excel 2003 is not supported by @RISK 7.x, files created by Excel 2003 are supported by @RISK 7.x in later versions of Excel.)
Additional keywords: Corrmat property function
Last edited: 2018-03-08