Array Formula Limations (MMULT, TRANSPOSE etc.)

Posted by Tom on November 17, 2001 8:11 PM

Excel 97 and Windows 98

I have created a procedure that builds matrices (square or rectangular arrays) and then performs mathematical operations using MMULT, MINVERSE, and TRANSPOSE etc. At some point (when a matrix reaches about 6,000 cells) the operation begins to return #VALUE for the array. According to Microsoft Q166342, the only limitation is RAM. Unfortunately, the 6,000 cells limitation appears to hold for computers with 64 or 512 of RAM. What is the true limitation on array formula calculations? And is there a work around?

<<Background information on question 2 -- A little background: Lognormal time series stock returns are generated for each component of a portfolio (the result of an external database query)- generally I am concerned with the most recent 60-months of data (all though this varies). The portfolio can be an actual portfolio or a list of user defined inputs - obviously the list can grow quite large. Thus the initial matrix is 60 rows by n columns. "asset_count" in this case is the number of columns. One of my goals is to make the spreadsheet as transparent as possible. The below code allows the user to see exactly where each figure originated.

' Creates the exponentially smoothed covariance matrix

Workbooks("Optimizer.xls").Worksheets("ReturnSummary"). _
Range(Cells(221, 1), Cells(221 + asset_count - 2, asset_count - 1)).Select

Selection.FormulaArray = "=MMULT(TRANSPOSE(R[-150]C[]: R[-90]C[" _
& asset_count - 2 & "]),R[-70]C[]:R[-10]C[" & asset_count - 2 & "])"

Workbooks("Optimizer.xls").Worksheets("SmoothedCovariance").Select

Workbooks("Optimizer.xls").Worksheets("SmoothedCovariance"). _
Range(Cells(2, 2), Cells(2 + asset_count - 2, asset_count)).Select

Selection.FormulaArray = "=12*MMULT(TRANSPOSE(ReturnSummary!R[69]C[-1]: R[129]C[" _
& asset_count - 3 & "]),ReturnSummary!R[149]C[-1]:R[209]C[" & asset_count - 3 & "])"

As a workaround to the 6,000 cell limit, I created a looping procedure that builds the matrix cell by cell - unfortunately it takes almost an hour to run and for some unknown reason each time I run it again it takes slightly longer than the time before.

For j = 0 To asset_count - 2

Application.StatusBar = "Building Exponentially Smoothed Covariance Matrix... Column " & j + 1 & _
" of " & asset_count - 1

For x = 1 To asset_count - 1

Workbooks("Optimizer.xls").Worksheets("SmoothedCovariance"). _
Range("B2").Offset(x - 1, j).FormulaArray = "=12*MMULT(TRANSPOSE(ReturnSummary!R[" & 70 - x & _
"]C[" & -1 & "]:R[" & 130 - x & "]C[" & -1 & "]),ReturnSummary!R[" & 150 - x & "]C[" & _
-2 + x - j & "]:R[" & 210 - x & "]C[" & -2 + x - j & "])"

Application.StatusBar = "Building Exponentially Smoothed Covariance Matrix... Column " & j + 1 & _
" of " & asset_count - 1 & " Row " & x

Next x

Next j

Your help is greatly appreciated. Thanks!

-Tom

Posted by Mark W. on November 19, 2001 3:40 PM

According to the Help Index topic "limits in
Microsoft Excel" the maximum size of worksheets
arrays are 6,553 elements.

Posted by Tom on November 19, 2001 7:41 PM

Thank you so much for taking your time to help me. If you visit the Microsoft web site they explain that the 6,553 number listed in the Help Index is not correct. They say...

<&LT;XL97: Maximum Array Size in Microsoft Excel 97

* Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel 97, the "Worksheet and workbook specifications" Help topic states that the maximum size of an array in a worksheet is 6,553 elements. This information is incorrect. This article explains the limitations of arrays in Microsoft Excel 97.>> Posted from http://support.microsoft.com/support/kb/articles/Q166/3/42.ASP?LN=EN-US&SD=gn&FR=0&qry=Q166342&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97

Posted by Mark W. on November 20, 2001 6:23 AM

I checked the Help topic for MMULT and it states
that it'll return #VALUE! if any inputs are blank
or text. By chance do any of your values have
more than 15 digits? If so, Excel will store
them as text. Thank you so much for taking your time to help me. If you visit the Microsoft web site they explain that the 6,553 number listed in the Help Index is not correct. They say...

: According to the Help Index topic "limits in : Microsoft Excel" the maximum size of worksheets : arrays are 6,553 elements.

Posted by Tom on November 20, 2001 8:41 PM

First, to answer your questions, it appears that most numbers have 15 digits - although they are all decimals. I believe the precision is important and would require serious stress testing and input from a Ph.D. or two.

The search for a speedy work-around continues. I found another article at the Microsoft web site that sheds additional light on my problem. (ARTICLE ID: Q177991) It says the maximum number of elements in array that can be passed "to Excel using the Excel Transpose function is 5461." This makes since because my procedure balks when the asset counts reaches 74. (74 x 74 = 5476) 5461 would appear to be the limit for a number of matrix math functions and cell-by-cell work-arounds are slow. Unfortunately I don't have the math or programming skills to parse the math into sections quickly.

Ah, I didn't know about this Help deficiency. I checked the Help topic for MMULT and it states that it'll return #VALUE! if any inputs are blank or text. By chance do any of your values have more than 15 digits? If so, Excel will store them as text. : Thank you so much for taking your time to help me. If you visit the Microsoft web site they explain that the 6,553 number listed in the Help Index is not correct. They say... : : <&LT;XL97: Maximum Array Size in Microsoft Excel 97 : The information in this article applies to